Forum Discussion
Aggregate differently by category
Let's say I have dozens of store units for which I have Revenue, Profit and Profitability values for each month since January 2016 (date format is DD/MM/YYYY):
| Unit | Type | Date | Value |
| AA | Revenue | 01/01/2016 | 1288000 |
| AA | Revenue | 01/02/2016 | 1335900 |
| AA | Revenue | 01/03/2016 | 2133400 |
| AA | Revenue | 01/04/2016 | 2564000 |
| .. | .. | .. | .. |
| AA | Revenue | 01/05/2018 | 2799900 |
| AA | Revenue | 01/06/2018 | 1164000 |
| AA | Revenue | 01/07/2018 | 2291900 |
| AA | Revenue | 01/08/2018 | 2297000 |
| AA | Revenue | 01/09/2018 | 1715900 |
| AA | Profit | 01/01/2016 | 166900 |
| AA | Profit | 01/02/2016 | -30600 |
| AA | Profit | 01/03/2016 | 934100 |
| AA | Profit | 01/04/2016 | 1111200 |
| .. | .. | .. | .. |
| AA | Profit | 01/05/2018 | 1649600 |
| AA | Profit | 01/06/2018 | 71000 |
| AA | Profit | 01/07/2018 | 965600 |
| AA | Profit | 01/08/2018 | 632000 |
| AA | Profit | 01/09/2018 | -2600 |
| AA | Profitability | 01/01/2016 | 13% |
| AA | Profitability | 01/02/2016 | -2% |
| AA | Profitability | 01/03/2016 | 44% |
| AA | Profitability | 01/04/2016 | 43% |
| .. | .. | .. | .. |
| AA | Profitability | 01/05/2018 | 59% |
| AA | Profitability | 01/06/2018 | 6% |
| AA | Profitability | 01/07/2018 | 42% |
| AA | Profitability | 01/08/2018 | 28% |
| AA | Profitability | 01/09/2018 | -0,2% |
| BB | Revenue | 01/01/2016 | 1454000 |
| .. | .. | .. | .. |
My report has a slicer for Unit. I want to create a matrix visual that would show Revenue, Profit and Profitability for each unit across years (note that Revenue and Profit are sums of values for all months within the year, while Profitability is simply Profit/Revenue). Selecting Unit = AA, the visual would show:
| 2016 | 2017 | 2018 | |
| Revenue | 21963900 | .. | 24644880 |
| Profit | 6544800 | .. | 3315600 |
| Profitability | 30% | .. | 13% |
(The above matrix is the result I want.)
However, when I create the visual in Power BI it aggregates all values by Sum and returns:
| 2016 | 2017 | 2018 | |
| Revenue | 21963900 | .. | 24644880 |
| Profit | 6544800 | .. | 3315600 |
| Profitability | 358% | .. | 161% |
Therefore, while the first two are correct, Profitability isn't.
Were I to aggregate values by Average, Profitability would be correct* and the other two wouldn't.
Is there any way I can aggregate Revenue and Profit by Sum, while aggregating Profitability by Average, all in the same visual?
* Note that for 2018, given the way my original data is designed, Profitability wouldn't be correct simply aggregated by Average, since October, November and December/2018 currently have null values which would affect the average. It would be best to be able to aggregate Profitability by average while ignoring zeros.
Hi Clara,
Yes there is.
You will need a measure though, like this one:
Value measure = IF ( FIRSTNONBLANK ( Table1[Type], 1 ) = "Profitability", AVERAGE ( 'Table1'[Value] ) , CALCULATE ( SUMX (Table1,('Table1'[Value]) ) ) )Instead of adding the value column as the value in matrix, use the Value measure instead.
It gives this result:
Note that the calculation is not correct. I just used your sample to have some test data.
Best regards
Kaj
3 Replies
Hi Clara,
Yes there is.
You will need a measure though, like this one:
Value measure = IF ( FIRSTNONBLANK ( Table1[Type], 1 ) = "Profitability", AVERAGE ( 'Table1'[Value] ) , CALCULATE ( SUMX (Table1,('Table1'[Value]) ) ) )Instead of adding the value column as the value in matrix, use the Value measure instead.
It gives this result:
Note that the calculation is not correct. I just used your sample to have some test data.
Best regards
Kaj
Anonymous Thank you so much! I've made one slight modification to your measure so as to ignore null values in the average, and got exactly what I wanted:
Value measure = IF ( FIRSTNONBLANK ( Table1[Type], 1 ) = "Profitability", CALCULATE ( AVERAGE ('Table1'[Value]), FILTER('Table1', 'Table1'[Value]<>0) ), CALCULATE ( SUMX (Table1,('Table1'[Value]) ) ) )
