Calculated Measure Based on Condition in Dax

Question

I have a requirement in Power Pivot where I need to show value based on the Dimension Column value.

If value is Selling Price then Amount Value of Selling Price from Table1 should display, if Cost Price then Cost Price Amount Should display, if it is Profit the ((SellingPrice-CostPrice)/SellingPrice) should display

My Table Structure is Table1:-

Table2:-

Required Output:-

If tried the below option:-

1. Calculated Measure:=If(Table[Category]="CostPrice",[CostValue],If(Table1[category]="SellingPrice",[SalesValue],([SalesValue]-[CostValue]/[SalesValue])))
*[CostValue]:=Calculate(Sum(Table1[Amount]),Table1[Category]="CostPrice")
*[Sales Value]:=Calculate(Sum(Table1[Amount]),Table1[Category]="SellingPrice")

Tried this in both Calculated Column and Measure but not giving me required output.

You might want to take a look at how this blog post uses the 'hasonevalue' option. powerpivotpro.com/2012/06/dax-making-the-case-for-switch — Lukasz P.

greggyb greggyb · Accepted Answer · 2016-01-15T14:06:58

Cost:=
CALCULATE(
    SUM( Table1[Amount] )
    ,Table1[Category] = "CostPrice"
)

Selling:=
CALCULATE(
    SUM( Table1[Amount] )
    ,Table1[Category] = "SellingPrice"
)

Profit:=
DIVIDE(
    [Selling] - [Cost]
    ,[Selling]
)

ConditionalMeasure:=
IF(
    HASONEFILTER( Table2[Category] )
    ,SWITCH(
        VALUES( Table2[Category] )
        ,"CostPrice"
        ,[Cost]
        ,"SellingPrice"
        ,[Selling]
        ,"Profit"
        ,[Profit]
    )
    ,[Profit]
)

HASONEFILTER() checks that there is filter context on the named field and that the filter context includes only a single distinct value.

This is just a guard to allow our SWITCH() to refer to VALUES( Table2[Category] ). VALUES() returns a table of all distinct values in the named column or table. So, a 1x1 table can be implicitly converted to a scalar, which we need in SWITCH().

SWITCH() is a case statement.

Our else condition in the IF() is just returning [Profit]. You might want something else, but it's unclear what should happen at the grand total level. You can leave this off, and the measure will be blank in IF()'s else condition.

Calculated Measure Based on Condition in Dax

2 Answers