Skip to main content
cancel
Showing results for 
Search instead 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

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

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

ManagementB.PNG



Thanks!

2 ACCEPTED SOLUTIONS
Stachu
Community Champion
Community Champion

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
Community Champion
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] ),
        '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])

 



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

View solution in original post

19 REPLIES 19
Artyom
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?RANK.png

v-jiascu-msft
Microsoft Employee
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.

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
Community Champion

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 🙂

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 groupProduct typeJanFebMarApr
TG1PT1100110  
TG1PT2200180  
TG2PT1300330  
TG2PT3400360  
Total 1000980  
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!

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

 



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

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

@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!

Perfect!

Thanks so much!!

Stachu
Community Champion
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] ),
        '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



Did I answer your question? Mark my post as a solution!
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 :
ff.PNG

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
Community Champion
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

basically instead of

ALL ( 'Classification'[Classification] )

you need to use

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

or

ALL('Classification')


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

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

@Stachu Thank You ...

Stachu
Community Champion
Community Champion

@Anonymous you're welcome Smiley Very Happy



Did I answer your question? Mark my post as a solution!
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

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Users online (1,251)