topic Re: Average in a matrix with different hierarchies in power bi in DAX Commands and Tips

Average in a matrix with different hierarchies in power bi

Yeimy — Wed, 28 Feb 2024 21:46:14 GMT

Hi,

I have a matrix with 4 levels, the fist level comes from a column of the date dim and the remainig 3 levels are from the produt dimension, i also have a measure called und sale wich is the sum of the sale units

What i want is to take the average, tha is, for my hormonal group the units of all the available months are added and divided by two (the average) then the same for the anti-pain group and so on for the others groups and the same for the others levels of hierarchy

I have usued these dax:

AVERAGEX(ALL(DIM_FECHA[M-Y]),

CALCULATE([Und Venta])

)

and

AVERAGEX(FILTER(VALUES(DIM_FECHA[M-Y]),[Und Venta]),

CALCULATE([Und Venta], REMOVEFILTERS(DIM_FECHA[M-Y])

)

But it doesn't show me the average but rather the current units or just the average of the total

Re: Average in a matrix with different hierarchies in power bi

Anonymous — Thu, 29 Feb 2024 04:35:11 GMT

Hi Yeimy ,

I created some data:

Are you referring to the different levels of Row subtotals showing the average for that level.

Measure follows the context of the "Total" row and is calculated in that context. Therefore, using a measure in a column of a table visualization may have unexpected values in the "Total" column.

You can consider using the switch() + Isinscope() function to determine the corresponding level in the matrix, and use the corresponding rule based on the corresponding level

SWITCH function (DAX) - DAX | Microsoft Learn
ISINSCOPE function (DAX) - DAX | Microsoft Learn

Here are the steps you can follow：

1. Create measure.

Measure = SWITCH( TRUE(), ISINSCOPE('Table'[Group4]),1, ISINSCOPE('Table'[Group3]),2, ISINSCOPE('Table'[Group2]),3, ISINSCOPE('Table'[Group1]),4,0)

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Average in a matrix with different hierarchies in power bi

Yeimy — Thu, 29 Feb 2024 16:00:46 GMT

Thank you,
But that doesn't solve my problem