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

     

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!