Forum Discussion
Relative Total and Subtotal Values
Hello All,
I have been stuck on this pickle of a problem for quite some time.
I would like to get the relative subtotal (and total) of the "% Gross Price Change" measure, at the most granular level.
In the below example the subtotal would equate to:
(0.1401 * (6637.82 / 6637.82 + 1864.19 + 13157.47) + 0.2303 * (1864.19 / 6637.82 + 1864.19 + 13157.47) + 0.1798 * (13157.47 / 6637.82 + 1864.19 + 13157.47 )) = 0.171 or 17,1
i.e each row where price change is available, "% Gross Price Change" / SUM of "CY Norm Gross Sales".
I suspect I will have to create an additional measure here but I am hoping someone has come across something like this before.
Any help is much appreciated. I understand this is a convoluted explanation, if there are further details required I am happy to provide them. It is also worth noting that both of these columns are themselves a combination of other measures.
All the best,
Luke
4 Replies
Anonymous This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907Also, MM3TR&R might be useful as well: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community
Greg_Deckler Thanks for the reply. I have read up diligently on measure totals and the common problems there. This is a slightly different problem in that the totals are not a straightforward "Summing" or average etc.
The triple threat solution may be on the right track. I will sift through and come back to you!
Thanks again,
Luke
Anonymous Looking at your formula more closely, perhaps something like the following:
VAR __Table = FILTER(SUMMARIZE(ALL('Table'),[Profit Center],[2nd Column],[3rd Column],[4th Column],[% Gross Price Change],"__CYNormGS",SUM('Table'[CY Norm Gross Sales])),[% Gross Price Change] <> BLANK()) VAR __Sum = SUMX(__Table,[__CYNormGS]) VAR __Table1 = ADDCOLUMNS(__Table,"__Sum%GPChange",__Sum) VAR __Table2 = ADDCOLUMNS(__Table1,"__Value",[% Gross Price Change] * [__Sum%GPChange]) RETURN SUMX(__Table2,[__Value])
Hi Anonymous,
Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.
If these also don't help, please share more detailed information to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
