Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everyone!
we are trying to build a report, in our organization, that pivots information about a specific group of people.
The model is being implemented, at this point, in Excel but will be migrated to Power BI in its due time.
The model we are implementing, is following the pattern posted by Marco Russo here with one only difference: it has more hierarchies levels.
The behavior of the model we are implementing is just the same as it is in the pattern above, however we would like to hide subtotals from the parent hierarchies when a specific hierarchy level is selected.
So, to make this little bit more clear, imagine that you have your model build like this (nothing selected on slicers):
When no slicers are selected the pattern works exactly how we want, as shown in the upper picture.
However, when we select a person from a lower level hierarchy, the behavior does not works how we would like.
In the lower picture, we show a selection of a person in the second level of the hierarchy, and we would like to build dax formulation that won't show the parent hierarchy subtotal:
So, our question is: how can one build a dax formulation, that shows brad's subtotal and show's blank's for his parents hierarchies (in this case its bill witch is highlighted) ?
I hope my explanation is clear enough, but if not, please ask me for more information.
Thanks in advance for your help and for reading this!
Hi @MPG758,
I write a formula and try to remove the summary value at top level, but it seem only works when its child node larger than 1.
Test = VAR current_level = COUNT ( Nodes[NodeKey] ) VAR all_level = COUNTX ( FILTER ( ALLSELECTED ( Nodes ), [Level1] = MAX ( [Level1] ) ), [NodeKey] ) RETURN IF ( current_level <> all_level, SUM ( Nodes[Amount] ) )
Maybe you can add a condition to check the current level if it is the top level and replace it to blank.
Regards,
Xiaoxin Sheng
Hi @v-shex-msft, and thanks for your reply/help!
I might be wrong, but i think he formulation you provides is not exactly how i would like the model to behave.
I only want to remove the top level total when, and only when, a person is selected/filtered in the slicer. Is this possible? I belive it is. 🙂
Also, this "(...) Maybe you can add a condition to check the current level if it is the top level and replace it to blank. (...)", is where i'm having troubles with. 🙂
Hi everyone!
I still struggle to find a solution for this issue.
I wonder if someone else has anything to add.
Thanks 🙂
Up! Up!
There must be a way of resolving this issue.
Does anyone has anything to add?
User | Count |
---|---|
131 | |
108 | |
101 | |
67 | |
64 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |