Forum Discussion
DieLem
8 years agoHelper II
Create calculated row
Good Day, I have a two-part request. I have a table with data in grouped in the "Classification" row, that has Current Month, YTD etc calculations done on the columns. I would like to add a COS...
- 8 years ago
try this
EDIT - removed inactive link, uploaded the sample file
- 8 years ago
assuming there is Tax% in classification table
you create a new measure defining the new ratioRatioTax := DIVIDE ( CALCULATE ( [RegularSum], ALL ( 'Classification'[Classification] ), 'Classification'[Classification] = "Trading Income" ), CALCULATE ( [RegularSum], ALL ( 'Classification'[Classification] ), 'Classification'[Classification] = "Taxation" ) )and adjust the final measure
SumAndRatio:= VAR varClassification = UPPER(IF(HASONEVALUE(Classification[Classification]),VALUES(Classification[Classification]),BLANK())) RETURN SWITCH(varClassification, "COS%",[RatioCoS], "Tax%",[RatioTax], [RegularSum] )
etc.
you could also consider extending the classification table with the definions of ratios, and use that as more general pattern, e.g.RatioFlag Classification Nominator Denominator FALSE Trading Income NA NA FALSE Cost of Sales NA NA FALSE Taxation NA NA TRUE CoS% Trading Income Cost of Sales TRUE Tax% Trading Income Taxation Ratio = VAR Nom = SELECTEDVALUE(Classification[Nominator]) VAR Denom = SELECTEDVALUE(Classification[Denominator]) RETURN DIVIDE ( CALCULATE ( [RegularSum], ALL ( 'Classification'[Classification] ), 'Classification'[Classification] = Nom ), CALCULATE ( [RegularSum], ALL ( 'Classification'[Classification] ), 'Classification'[Classification] = Denom ) )SumAndRatio = VAR varRatioFlag = IF(HASONEVALUE(Classification[RatioFlag]),VALUES(Classification[RatioFlag]),BLANK()) RETURN IF(varRatioFlag,[Ratio],[RegularSum])
Stachu
8 years agoCommunity Champion
try this
EDIT - removed inactive link, uploaded the sample file
Anonymous
6 years agoNot applicable
Thanks for the solution and this solution works! Can you please reshare the sample pbix file as the one drive link is broken!