Solved: Create calculated row - Page 2

DieLem · ‎06-15-2018

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% which is [Trading Income] / [Cost of Sales]. However I don't know how to do it without creating the measure for Current Month, YTD etc. I want to create it once and then slot it in, same way as the Trading Income interacts with the measures.

Secondly, is it possible to add the new calculated row "COS%" to display in the Classification row with the other predetermined values (i.e. Trading Income, Cost of Sales etc)

See images below for the requirements.

and it should slot in between as below:

Thanks!

Stachu · ‎06-29-2018

try this

EDIT - removed inactive link, uploaded the sample file

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

Stachu · ‎07-12-2018

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

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

Create calculated row

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly