Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a hierarchy table and would like to display total based on what level the slicer is selected.
If all Level 3 (Division) checkboxes selected, display sum of Level 2 (BU) total across all groups
If all Level 2(BU) checkboxes selected, display sum of Level 1(Group) total across all group
If not all Level 3 checkboxes selcted (regardless of BU), display selected Level 3 (Division) total across all BUs
If not all Level 2 checkboxes selcted (regardless of Group), display selected Level 2 (BU)total across all groups.
Below are some examples:
If Audit, SPU, YAD selected, display sum of (Audit, SPU, YAD)
If 7JB and F8C selected, display sum of (7jb and f8c)
If OC9 selected and Audit selected, display sum of (DIM, TIF and Audit)
This is the formula that I currently have. It works for Divsion level and Group (Group works when no selection is made in the hierarchy)
myTotal = if (ISFILTERED(DataView[DIVISION]),[DIVISION_AMT],if(ISFILTERED(DataView[BU]),[BU_AMT],[GROUP_AMT]))
Going to need example data for this one. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
That being said, take a look at my Matrix Measure Total Triple Threat Rock & Roll, it solves a similar problem to this and the techniques used might apply.
Hi @Anonymous,
Unfortunately, it is really impossible to meet your requirement. However we can add normal slicers and create three measures to work around. Here are the formulas.
sum Div = IF(calculate(distinctcount([DMSION]),allselected(Table1[DMSION]))=CALCULATE(DISTINCTCOUNT(Table1[DMSION]),ALL(Table1)),SUM(Table1[BU_AMT]),CALCULATE(SUM(Table1[DIVISION_AMT]),ALLSELECTED(Table1[DMSION])))
group sum = IF(CALCULATE(DISTINCTCOUNT(Table1[Group]),ALLSELECTED(Table1[Group]))=CALCULATE(DISTINCTCOUNT(Table1[Group]),ALL(Table1)),SUM(Table1[GROUP_AMT]),CALCULATE(SUM(Table1[GROUP_AMT]),ALLSELECTED(Table1[Group])))
BU sum = IF(CALCULATE(DISTINCTCOUNT(Table1[BU]),ALLSELECTED(Table1[BU]))=CALCULATE(DISTINCTCOUNT(Table1[BU]),ALL(Table1)),SUM(Table1[GROUP_AMT]),CALCULATE(SUM(Table1[BU_AMT]),ALLSELECTED(Table1[BU])))
For more details, please check the pbix as attached.
https://www.dropbox.com/s/ib2a3myc5fafbc6/suma2.pbix?dl=0
Regards,
Frank
Thanks for the feeback. I'll have to present this as an alternate. Do you think this can be accomplished with M Query? I've never done anything using M Query.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |