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...

Need Help

Stachu
8 years ago
try this

EDIT - removed inactive link, uploaded the sample file

20180629_PBI_Ratio.pbix45 KB

Stachu

8 years ago

assuming there is Tax% in classification table
you create a new measure defining the new ratio

RatioTax :=
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])

v-jiascu-msft

Microsoft Employee

8 years ago

Hi DieLem,

Did Stachu's solution work?

Best Regards,

Dale

DieLem

Helper II

8 years ago

Good Day v-jiascu-msft,

No the example Stachu did not work. Perhaps I am making a mistake. Is it possible to create a example of the above request with simple set of data so I can see how it's done?

Thanks!

Stachu
Community Champion
8 years ago
try this

EDIT - removed inactive link, uploaded the sample file

20180629_PBI_Ratio.pbix45 KB
- DieLem
  Helper II
  8 years ago
  Hey Stachu,
  
  Your solution works, thanks so much!!
  
  I have one more questions in terms of scalability:
  
  How do I add a second or third custom calculation (For example Tax% -Using your data just Trading Income / Taxation) so that it is included in the table? Thus:
  
  CoS% 0.75 etc
  Cost of sales 1.20 etc
  Taxation 0.2 etc
  Tax% 6.0 etc
  ...and so forth.
  
  I attempted to add it but to no avail. Table displays only the one or the other.
  
  Thanks in advance!
  - Stachu
    Community Champion
    8 years ago
    assuming there is Tax% in classification table
    you create a new measure defining the new ratio
    RatioTax := 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])
- Anonymous
  Not applicable
  6 years ago
  Thanks for the solution and this solution works! Can you please reshare the sample pbix file as the one drive link is broken!
- nfriis
  New Member
  2 years ago
  Hi Stachu
  This is brilliant.
  What if you have a matrix and want to control two different variables/colomns, for example a column with target group and a column with product types and want a table that could look like this?
  
  Target group Product type Jan Feb Mar Apr
  TG1 PT1 100 110
  TG1 PT2 200 180
  TG2 PT1 300 330
  TG2 PT3 400 360
  Total 1000 980
  PT1% 40% 44%
  PT2% 20% 18%
  PT3% 40% 36%
  GT1% 30% 31%
  GT2% 70% 69%

Forum Discussion

Create calculated row

Related content

Create rows from 2 tables

Row wise calculation

DAX Measure for Ranking the valid rows and calculating the average rating

Need help to create Calculation in DAX

Matrix - Can Primary Row Be It's Own Calculation From Sub-Rows?

Target group	Product type	Jan	Feb	Mar	Apr
TG1	PT1	100	110
TG1	PT2	200	180
TG2	PT1	300	330
TG2	PT3	400	360
Total		1000	980
PT1%		40%	44%
PT2%		20%	18%
PT3%		40%	36%
GT1%		30%	31%
GT2%		70%	69%