cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DieLem Regular Visitor
Regular Visitor

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

ManagementA.PNG
and it should slot in between as below:

ManagementB.PNG



Thanks!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Create calculated row

Super User
Super User

Re: Create calculated row

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.

RatioFlagClassificationNominatorDenominator
FALSETrading IncomeNANA
FALSECost of SalesNANA
FALSETaxationNANA
TRUECoS%Trading IncomeCost of Sales
TRUETax%Trading IncomeTaxation

 

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

 

9 REPLIES 9
Super User
Super User

Re: Create calculated row

it's a bit complex but doable
as they are no calculated rows, you will need to create a new table such as this
Classification

Trading Income
Cost of Sales
...
Taxation
COS%

and create a join to your original table Classification column

 

Now the measures - I assume the current Actual/Budget measures are something like this:

Current Month Budget:=CALCULATE(SUM('Table'[Value]),'Table'[Scenario]="Current Month Budget")

we will need to modify the blue part for this, and create a separate measure for it (whatever is your equivalent would suffice)

RegularSum:=SUM('Table'[Value])

then define the ratio (join must be in place for it to work)

 

RatioCos :=
DIVIDE (
    CALCULATE (
        [RegularSum],
        ALL ( 'Classification'[Classification] ),
        'Classification'[Classification] = "Trading Income"
    ),
    CALCULATE (
        [RegularSum],
        ALL ( 'Classification'[Classification] ),
        'Classification'[Classification] = "Cost of Sales"
    )
)

then we merge the two

SumAndRatio:=
VAR varClassification = UPPER(IF(HASONEVALUE(Classification[Classification]),VALUES(Classification[Classification]),BLANK()))
RETURN
SWITCH(varClassification,"COS%",[RatioCoS],[RegularSum])

with this in place the original Budget measure would look like this

Current Month Budget:=CALCULATE([SumAndRatio],'Table'[Scenario]="Current Month Budget")

 Assuming Variance measure is just Actuals - Budget it should work as intended without any change

Community Support Team
Community Support Team

Re: Create calculated row

Hi @DieLem,

 

Did @Stachu's solution work?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DieLem Regular Visitor
Regular Visitor

Re: Create calculated row

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!

Super User
Super User

Re: Create calculated row

DieLem Regular Visitor
Regular Visitor

Re: Create calculated row

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!

Super User
Super User

Re: Create calculated row

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.

RatioFlagClassificationNominatorDenominator
FALSETrading IncomeNANA
FALSECost of SalesNANA
FALSETaxationNANA
TRUECoS%Trading IncomeCost of Sales
TRUETax%Trading IncomeTaxation

 

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

 

DieLem Regular Visitor
Regular Visitor

Re: Create calculated row

Perfect!

Thanks so much!!

Highlighted
jatneerjat Member
Member

Re: Create calculated row

Hi @Stachu,

 

How can i add 2 rows in my table in which one row shows sum all values as 'T' and other row will shows sum of only values in bottom 4 rows as 'F'.

Here stage is coming from a table and 'current','previous','%change' are measures

1.PNG

 

Artyom Visitor
Visitor

Re: Create calculated row

Good day,

I have one problem with the calculated row.

When I added a special column (rank 1,2,3,4 ...) and tried to sort the classification by this column, the lines added earlier rows(COS%, and others) disappear from the report.

How to fix this problem?RANK.png