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 ...
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
v-deddai1-msft
Community Support
Community SupportHi,
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