Forum Discussion
Solved
DAX Create measure with multiple aggregations over 2 columns
Hello,
I am trying to create a DAX measure with following table.
| Column A | Column B | Column C | Date | Value |
| 1 | 1 | P1 | 10/26/2017 | 10 |
| 1 | 1 | P1 | 10/27/2017 | 20 |
| 1 | 3 | P3 | 10/28/2017 | 10 |
| 2 | 2 | P1 | 10/29/2017 | 15 |
| 2 | 2 | P2 | 10/30/2017 | 25 |
I want to dislpay the following grid data.
| Column A | Column B | Value |
| 1 | 1 | 20 |
| 1 | 3 | 10 |
| 2 | 2 | 40 |
The measure is calculated as follows: It is a 2 step aggregation
Get the max value for each of the first 3 column values. i.e after the 1st aggregation the result would look like:
| Column A | Column B | Column C | Value |
| 1 | 1 | P1 | 20 |
| 1 | 3 | P3 | 10 |
| 2 | 2 | P1 | 15 |
| 2 | 2 | P2 | 25 |
Now sum it over first 2 columns to get the final result which is:
| Column A | Column B | Value |
| 1 | 1 | 20 |
| 1 | 3 | 10 |
| 2 | 2 | 40 |
Help me in creating the DAX measure which would result in the above data when I create a table visual in Power BI. Thanks in advance..
You can try
sum = VAR first_agg_tbl = SUMMARIZE ( yourTable, yourTable[Column C], "Max of each column C", MAX ( yourTable[Value] ) ) RETURN SUMX ( first_agg_tbl, [Max of each column C] )
2 Replies
You can try
sum = VAR first_agg_tbl = SUMMARIZE ( yourTable, yourTable[Column C], "Max of each column C", MAX ( yourTable[Value] ) ) RETURN SUMX ( first_agg_tbl, [Max of each column C] )Thank you Eric. It was working great..
