Forum Discussion
Trying to avoid aggregation over two way hierarchies
Hi - I need help with a hierarchy table. I am trying to show results by two-way hierarchy. So for example, the Dimensions (Parent) and Questions (Child), both have individual results (not to be aggregation by Power BI).
And also want to see these results by Markets (Parent) and Countries (Child). I applied this formula, which I found as a solution for avoiding aggregation in hierachy. However this only works for dimension (parent) and question (child), when I try to apply this to Markets and Countries it doesn't work (https://community.powerbi.com/t5/Desktop/Matrix-hierarchy-sum-issue/m-p/1197272#M535992)
Measure =
IF (
ISINSCOPE ( 'Table'[level4] ),
SUM ( 'Table'[Visitor] ),
IF (
ISINSCOPE ( 'Table'[level3] ),
CALCULATE (
VALUES ( 'Table'[Visitor] ),
FILTER ( ALL ( 'Table' ), 'Table'[Web] = MAX ( 'Table'[level3] ) )
),
IF (
ISINSCOPE ( 'Table'[level2] ),
CALCULATE (
VALUES ( 'Table'[Visitor] ),
FILTER ( ALL ( 'Table' ), 'Table'[Web] = MAX ( 'Table'[level2] ) )
),
CALCULATE (
VALUES ( 'Table'[Visitor] ),
FILTER ( ALL ( 'Table' ), 'Table'[Web] = MAX ( 'Table'[level1] ) )
)
)
)
)
I would like to be able to see the results by EMEA, and then drill down and see the results by market and then drill down and see the results by country. However right now, the measure only works if I have one Market/Country selected on a slicer. Error message received: MdxScript(Model) (6,204) Calulation error in measure: A table of multiple values was supplied wha a single value was expected.
Is there any additional measures I can make that will allow me to drill down by another hierarchy?
1 Reply
- amitchandak
Super User
Anonymous , IsInscope can help you change measure based on level
IsInScope - Switch measure at different levels: https://youtu.be/kh0gezKICEM
You can change axis using field parameters and then measure based on selected Axis
Get Super Dynamic TOPN using Window, Numeric, and Field parameters along with calculation groups- https://youtu.be/YW1SOk9V9xk
Switch TOPN with Field Parameters: https://amitchandak.medium.com/switch-topn-with-field-parameters-299a0ae3725f