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

     

DieLem's avatar
DieLem
Helper II
8 years ago

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

     

    • DieLem's avatar
      DieLem
      Helper II
      8 years ago

      Perfect!

      Thanks so much!!

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

    • dokat's avatar
      dokat
      Post Prodigy
      4 years ago

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