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

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

     

20 Replies

  • Stachu's avatar
    Stachu
    Community Champion
    8 years ago

    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

    • jatneerjat's avatar
      jatneerjat
      Helper V
      7 years ago

      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

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi ,

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

      Regards
      PD

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Stachu
        Community Champion
        7 years ago

        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')
    • DieLem's avatar
      DieLem
      Helper II
      8 years ago

      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!

  • Artyom's avatar
    Artyom
    Regular Visitor
    7 years ago

    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?