How to create a measure depending on the hierarchy level in the matrix?

[Harris]

Please help, how to create a measure depending on the hierarchy level in the matrix?

[Harsh]

Hello @Harris,

Thank You for posting your query onto the Forum.

Well I’m providing a link of a video which was created during one of the “Learning Summit Series”. Although the video is related to the “Ranking Techniques” but it has incorporated the concept of “Hierarchical Problems” which is very much applicable in this scenario.

Using this technique as shown in the video you can solve this type of problem that you’re facing.

Hoping you find this useful and helps you to solve your problem.

Thanks and Warm Regards,
Harsh

Ranking Techniques

[MK3010]

Hi @Harris,

You can also refer to below link where we have DAX calculation for hierarchy

How to do Conditional Format referring to group average - #9 by MK3010

How to do Conditional Format referring to group average Part II

Hi @ammu Hope the above updated DAX code is what you were looking. I have used with the new dataset and it is skipping cost center 1008. Below screen shot FYR. [image]

How to do Conditional Format referring to group Net result average

Hi @ammu, See if this helps you resolve it. Av. multi = SWITCH( TRUE(), ISINSCOPE( Facility[Code] ), AVERAGEX( CALCULATETABLE( VALUES( Facility[Code] ), ALLSELECTED( Facility[Code] ) ), [Net Result] ), ISINSCOPE( Facility[RAC Grouping] ), AVERAGEX( ALLSELECTED( Facility[RAC Grouping] ), [Net Result] ), [AverageNR] )

[BrianJ]

@Harris,

Following on to @MK3010’s post, the eDNA knowledgebase entry for ISINSCOPE has some great video and forum links:

https://info.enterprisedna.co/dax-function-guide/isinscope/

Other functions that are less commonly used for this purpose but also still helpful in special cases are ISFILTERED and HASONEFILTER.

https://info.enterprisedna.co/dax-function-guide/isfiltered/

https://info.enterprisedna.co/dax-function-guide/hasonefilter/

I hope this is helpful to you.

• Brian

[AntrikshSharma]

@Harris Refer to my answer here:

Re: Seeking a dax formula

[EnterpriseDNA]

A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!