Get averages on totals and Subtotals in Power BI Matrix

[LamSar]

Good afternoon,

On rowlevel of the matrix I have Chain, Shop, MonthName

On the values I have the measure [Sales]

An example of dummy data:

- on Month Level I have to display the measure [Sales]

- On the level of the shop: I need to display the average of the month level. For example for Shop 3: SUM of sales of January and fabruaru for shop 3 divided by the number of months (in this case 2)

- On the level of chain I need the average of the subtotals of the shops that belong to that chain. So for chain A:  Shop1 + Shops 2 / number of shops (in this case 2)

- On the grand total level: I need the average of the subtotals of the chains. So grandtotal is: Chain A + Chain B / 2 (the number of chains).

Is it possible to make this calculation in 1 measure instead of having to create 4?

If yes, how?

[visheshjain]

Hi @LamSar,

My suggestion would be to create all the measure that you want displayed and then use the ISINSCOPE() function to display the measure depending on which column in being used.

Something on the lines of:

SWITCH(
TRUE(),
ISINSCOPE(Dim[Chain], [Average Sales]),
ISINSCOPE(Dim[Shop], [Average Shop Sales]),
Sales
)

Apologies if my syntax is wrong, but you get the gist.

This way you can control, which measure is being shown at which level of the matrix.

Hope this helps!