Add averages in a hierarchy

[Syndicate_Admin]

Hello everyone

I have a problem with calculating the sum of averages but, as much as I have searched, I have not found anything that will solve it.

I want to get some salary data, that at the lowest level of the hierarchy the average value of the employees comes out but as I go up in the hierarchy, I add those values and do not give me the average of those that are below.

That is, at the lowest level of the Remuneration Type hierarchy, I want you to show me the averages (I used an average measure of value) but, up the hierarchy, I want to have the sum of those averages. I have tried with a conditional measure but I do not know with what function to get that I add the data from the average.

In my example, the objective data I would need is the ones I have marked in green (in yellow which gives me that it is not correct):

I hope you have explained me well and thank you very much for reading this message.

Best regards

Berta

[v-jayw-msft]

Hi @Syndicate_Admin ,

You could create different measures to get correct results for each hierarchy. Then use ISINSCOPE() or ISFILTERED() function to return different measure for corresponding hierarchy.

For example:

Measure =
VAR avg1 =
    AVERAGEX ( ALLEXCEPT ( 'table', 'table'[hierarchy1] ), 'table'[value] )
VAR avg2 =
    AVERAGEX ( ALLEXCEPT ( 'table', 'table'[hierarchy2] ), 'table'[value] )
VAR avg3 =
    AVERAGEX ( ALLEXCEPT ( 'table', 'table'[hierarchy3] ), 'table'[value] )
RETURN
    SWITCH (
        TRUE (),
        ISFILTERED ( 'table'[hierarchy1] ), avg1,
        ISFILTERED ( 'table'[hierarchy2] ), avg2,
        ISFILTERED ( 'table'[hierarchy3] ), avg3
    )

Best Regards,

Jay

[Syndicate_Admin]

Good morning

I have tried the solution that you have passed me, but I think I do something wrong because I do not recognize the ISFILTERED function. I don't know if I don't have the hierarchy well created or I don't refer to it correctly. (I'm trying with the median but with the averagex the same thing happens to me)

Median Salary =

WHERE med1 =

MEDIANX ( ALLEXCEPT ( 'Remuneration Data', 'Remuneration Data'[05 Remuneration Concept]), 'Remuneration Data'[06 Value])

WHERE med2 =

MEDIANX ( ALLEXCEPT ( 'Remuneration Data', 'Remuneration Data'[04 Remuneration Breakdown] ), 'Remuneration Data'[06 Value] )

WHERE med3 =

MEDIANX( ALLEXCEPT ( 'Remuneration Data', 'Remuneration Data'[03 Remuneration Type] ), 'Remuneration Data'[06 Value] )

RETURN

SWITCH (

TRUE (),

ISFILTERED ( 'Remuneration Data'[05 Remuneration Concept] ), med1,

ISFILTERED ( 'Remuneration Data'[04 Remuneration Breakdown] ), med2,

ISFILTERED ( 'Remuneration Data'[03 Remuneration Type]), med3

)

Thank you!!

[Syndicate_Admin]

First of all, sorry for having taken so long to answer. I'm pretty clumsy on the forum.

Thank you very much for the reply.

Does it also work for medium-sized companies? With Medianx?

Greetings and thank you very much again.

[amitchandak]

@Syndicate_Admin , Not very clear.

But If In need Avg Above employee and sum before that

then AverageX(Values(Employee[Employee]), [Sum Measure])

or you can use isinscope to switch measures

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/