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.
Hello and thank you all, who helped me with other issues (I have never posted here before, but I found so many solutions for my tasks)!
Info:
here is a data model
Pivot table of my report looks like this
Problem:
there are two measures (divide_TEST and total_divide_TEST which contains the first one),
divide_TEST:=DIVIDE( CALCULATE( SUM(DATA[value_bgly]);DATA[index]="Gross revenue"); CALCULATE( SUM(DATA[value_bgly]);DATA[index]="Volume (bottles)"); 0)
total_divide_TEST:=IF( HASONEVALUE(DATA[name_reporting]); [divide_TEST]; SUMX(SUMMARIZE( DATA; DATA[name_reporting]; DATA[sales_area]; "__TOTAL__";[divide_TEST]);[__TOTAL__]) )
but neither one do not works as I need → Subtotal and Grand total are not the sum of rows.
But, when I'll rotate my PT, somethings gonna work partially:
1st measure shows Subtotal and Grand total everywhere, but the wrong one unlike the 2nd measure, which shows Subtotal and Grand total only in "Gross revenue" and "Totals" section. And even only section "Totals" shows the correct evaluation of Subtotal and Grand total of 2nd measure. I've spent a lot of time to solve summarization of division in row line, but I've no deeper knowledges and skills to work with hierarchy, and I think the problem is in it.
Help me please to resolve this task! (file)
J=D
Solved! Go to Solution.
Hi @xkoby001
You may create below measures to get correct total for measure [divide_TEST] and [total_divide_TEST].You may turn off the column subtotals.
Reference:https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/td-p/547907
Measure = SUMX(VALUES(DATA[name_reporting]),[divide_TEST])
Measure 2 = SUMX(VALUES(DATA[name_reporting]),[total_divide_TEST])
Regards,
Hi @xkoby001
You may create below measures to get correct total for measure [divide_TEST] and [total_divide_TEST].You may turn off the column subtotals.
Reference:https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/td-p/547907
Measure = SUMX(VALUES(DATA[name_reporting]),[divide_TEST])
Measure 2 = SUMX(VALUES(DATA[name_reporting]),[total_divide_TEST])
Regards,
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 |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |