Forum Discussion
Participation inside a Hierarchy
I have a dataset with a hierarchy and value, something like this:
| Level 1 | Level 2 | Level 3 | Value |
| a | category 1 | class 1 | 300 |
| a | category 1 | class 2 | 200 |
| a | category 2 | class 1 | 500 |
I need to know how I could see the % respected each level inside a Descomposition Tree or Matrix to display it in the same column.
For example if I select only level 1 and level 3 I have to display 80% for class 1, but if I select the levels 1 and 2 I have to see 50% in category 1.
How I could get it?
Hi,
You can use ISINSCOPE function to calculate it:
Measure = VAR A = CALCULATE ( SUM ( 'Table'[Value] ), ALL ( 'Table' ) ) RETURN IF ( ISINSCOPE ( 'Table'[Level 2] ), IF ( ISINSCOPE ( 'Table'[Level 3] ), SUM ( 'Table'[Value] ) / A, CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', 'Table'[Level 2] IN DISTINCT ( 'Table'[Level 2] ) ) ) / A ), CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', 'Table'[Level 1] IN DISTINCT ( 'Table'[Level 1] ) ) ) / A )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
2 Replies
- amitchandak
Super User
Anonymous , refer if this can help
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
v-deddai1-msftCommunity Support
Hi,
You can use ISINSCOPE function to calculate it:
Measure = VAR A = CALCULATE ( SUM ( 'Table'[Value] ), ALL ( 'Table' ) ) RETURN IF ( ISINSCOPE ( 'Table'[Level 2] ), IF ( ISINSCOPE ( 'Table'[Level 3] ), SUM ( 'Table'[Value] ) / A, CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', 'Table'[Level 2] IN DISTINCT ( 'Table'[Level 2] ) ) ) / A ), CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', 'Table'[Level 1] IN DISTINCT ( 'Table'[Level 1] ) ) ) / A )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
