Create calculated row

[DieLem]

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 · Accepted solution]

try this

EDIT - removed inactive link, uploaded the sample file

[Artyom]

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?

[v-jiascu-msft]

Hi @DieLem,

Did @Stachu's solution work?

Best Regards,

Dale

[DieLem]

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 · Accepted solution]

try this

EDIT - removed inactive link, uploaded the sample file

[nfriis]

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%

[Anonymous]

Thanks for the solution and this solution works! Can you please reshare the sample pbix file as the one drive link is broken!

[DieLem]

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 · Accepted solution]

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

[JJ69Lyon]

Hello, I want to build the same report, but the ratio did not display... It's the same dax formulas... Is it possible to analyse my file ? Tks...fichier source pbix 

[dokat]

@Stachu Thanks for the directions, solution worked for me as well

[tomtang]

@Stachu 

Thanks for sharing the information.

I've copied the approach in my dashboard and the ratios are really calculating automatically.

However, when I am trying to visualize in Matrix, the row of the ratios still empty.

Like in snapshot1 (left side is MTD, the right side is YTD), the ratios are blank.

But when I click "Gross Margin%" in YTD matrix, the ratio pops up in MTD matrix at the total level

Below are the DAX I built in my dashboard:

1. Simply sum up dollar value

*SignedData_USD_Sum = Sum(BUCONSOL_Combined[*SignedData_USD])

2. Calculate ratios

*Metrics_AllRatio =

VAR Nom = SELECTEDVALUE(MatrixMap[Nominator])

VAR Denom = SELECTEDVALUE(MatrixMap[Denominator])

RETURN

FORMAT(

DIVIDE(

CALCULATE([*SignedData_USD_Sum],ALL(MatrixMap[Tech_Name]),MatrixMap[Tech_Name] = Nom),

CALCULATE([*SignedData_USD_Sum],ALL(MatrixMap[Tech_Name]),MatrixMap[Tech_Name] = Denom)

),"Percent"

)

3. DAX decide which classification to show ratio or dollar value

*SignedDataAndRatio =

VAR varRatioFlag = UPPER(IF(HASONEVALUE(MatrixMap[RatioFlag]),VALUES(MatrixMap[RatioFlag]),BLANK()))

RETURN

IF(varRatioFlag,[*Metrics_AllRatio],[*SignedData_USD_Sum])

Do you happen to know any error I might make?

Thanks!

[DieLem]

Perfect!

Thanks so much!!

[Stachu]

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

[Anonymous]

Hi ,
I have created classification table want sorting of classification but when i sort classification by order then calculated rows are not showing values ,
How to sort classification column with  all Values.

Below is my classification table with order :


In this kpis column yield% and Total input are mesure or insrted rows :
when I try to sort kpis by order then yield% and Total input becomes null, why ?how to resolve this issue ?

Regards ,
Pooja

[Stachu]

when custom sort order is used the order column is treated as if it was added to the visual, you need to account for that in the code

basically instead of

ALL ( 'Classification'[Classification] )

you need to use

ALL ( 'Classification'[Classification], 'Classification'[Order] )

or

ALL('Classification')

[tomtang]

@Stachu 

Hi, the empty calculated rows are addressed by this post (lol, I just saw it after I posted the question to you)

Just wondering why it happens to happen to be empty if I sort the classification by another column?

Thanks!

[Anonymous]

@Stachu Thank You ...

[Stachu]

@Anonymous you're welcome 

[Anonymous]

Hi ,

How to sort classfiction by perticular order , becouse of switch case ,can not sort by classification order.

Regards
PD

[jatneerjat]

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