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

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

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')
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Stachu Thank You ...

      • Stachu's avatar
        Stachu
        Community Champion
        7 years ago

        Anonymous you're welcome :smileyvery-happy:

    • tomtang's avatar
      tomtang
      Helper III
      6 years ago

      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!