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
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
Icon for Community Champion rankCommunity 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])