Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 granula...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
4 years ago

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/547907

 

Also, MM3TR&R might be useful as well: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      4 years ago

      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])