Forum Discussion

DieLem's avatar
DieLem
Helper II
8 years ago
Solved

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...
Need Help
  • Stachu's avatar
    Stachu
    8 years ago

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

     

Stachu's avatar
Stachu
Community Champion
8 years ago

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

 

tomtang's avatar
tomtang
Helper III
6 years ago

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!