How to SUM a Column based on another column in DAX

问题:

Here is the example,

+------+----+
| CARS |  3 |
| CARS |  4 |
| CARS |  6 |
| CARS | 76 |
| CARS |  4 |
| CARS |  3 |
| SUV  | 32 |
| SUV  |  3 |
| SUV  |  3 |
| SUV  |  2 |
| SUV  |  3 |
| SUV  |  5 |
+------+----+

I want to add a new calculated column with data like this

+------+----+------+
| CARS |  3 |  96  |
| CARS |  4 |  96  |
| CARS |  6 |  96  |
| CARS | 76 |  96  |
| CARS |  4 |  96  |
| CARS |  3 |  96  |
| SUV  | 32 |  48  |
| SUV  |  3 |  48  |
| SUV  |  3 |  48  |
| SUV  |  2 |  48  |
| SUV  |  3 |  48  |
| SUV  |  5 |  48  |
+------+----+------+ 

Where it would sum up the number column for the type of vehicle. What would be the DAX command to get this? I need a third column that sums up all Cars..that is 96 for the CARS row, and sums up all Suvs, that is 48 for all rows that are suvs. Please help

回答1:

You can create a calculated measure to accomplish this. I imported your data into a Power Pivot model. Then I added the following calculated measure:

Type Subtotal:=CALCULATE( SUM( [Value]), ALLEXCEPT(Table2, Table2[Type]))

So when I make a pivot table I get the desired result.