cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper 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% 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!

2 ACCEPTED SOLUTIONS
Community Champion

try this

Thank you for the kudos 🙂

Community Champion

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

```RatioTax :=
DIVIDE (
CALCULATE (
[RegularSum],
ALL ( 'Classification'[Classification] ),
),
CALCULATE (
[RegularSum],
ALL ( 'Classification'[Classification] ),
'Classification'[Classification] = "Taxation"
)
)```

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

Thank you for the kudos 🙂

20 REPLIES 20
Regular Visitor

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?

Microsoft Employee

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

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!

Community Champion

try this

Thank you for the kudos 🙂

New Member

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
Not applicable

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

Helper II

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.

Community Champion

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

```RatioTax :=
DIVIDE (
CALCULATE (
[RegularSum],
ALL ( 'Classification'[Classification] ),
),
CALCULATE (
[RegularSum],
ALL ( 'Classification'[Classification] ),
'Classification'[Classification] = "Taxation"
)
)```

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

Thank you for the kudos 🙂

New Member

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

Post Prodigy

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

Helper III

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!

Helper II

Perfect!

Thanks so much!!

Community Champion

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

Thank you for the kudos 🙂

Anonymous
Not applicable

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

Community Champion

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

`ALL ( 'Classification'[Classification] )`

you need to use

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

or

`ALL('Classification')`

Thank you for the kudos 🙂

Helper III

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
Not applicable

@Stachu Thank You ...

Community Champion

@Anonymous you're welcome

Thank you for the kudos 🙂

Anonymous
Not applicable

Hi ,

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

Regards
PD

Helper V

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors