Aggregate differently by category

[Clara]

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.

[Anonymous · Accepted solution]

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

[Clara]

@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])
            )
        )
    )

[Anonymous]

You're most welcome! Glad I could help 🙂