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

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