locked
Calculated Measure Based on Condition RRS feed

  • Question

  • Hi All,

    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:-

    My Report Output should be look like 

    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.

    Can anybody help me on this.

    Your Help would be appreciated

    Regards,

    Piyush Jain


    Thursday, September 3, 2015 3:47 PM

Answers

  • The suggestion above works for a calculated column, but not for a calculated field. This is because in a calculated field you cannot directly reference a column. Instead, it should be aggregated.

    I would add a Code column to your Table2 with values 1, 2 and 4 for CostPrice, SellingPrice and Profit, respectively. You can then create a calculated field like

    Result:=SWITCH(SUM(Table2[Code]),1,[CostValue],2,[SellingValue],4,[Profit])

    You should NOT have a relationship between Table1 and Table2 in this case. This would complicate matters because in [Profit] you need both the Table1 rows for cost value and for selling value.

    • Proposed as answer by Charlie Liao Tuesday, September 22, 2015 8:56 AM
    • Marked as answer by Charlie Liao Thursday, October 8, 2015 9:29 AM
    Tuesday, September 15, 2015 11:07 AM
    Answerer
  • Your simplest solution is to denormalize your data model, including a separate column for each of CostPrice, SellingPrice, and Profit. You can then use these with much simpler functions.

    For your specific use case, you can use your Category table as follows:
    Measure:=
    IF(
      HASONEVALUE(Table2[Category])
      ,SWITCH(
        VALUES(Table2[Category])
        ,"CostPrice", SUM(Table1[CostPrice])
        ,"SellingPrice", SUM(Table1[SellingPrice])
        ,"Profit", SUM(Table1[Profit])
      )
      ,BLANK()
    )
    



    GNet Group BI Consultant

    • Proposed as answer by Charlie Liao Tuesday, September 22, 2015 8:56 AM
    • Marked as answer by Charlie Liao Thursday, October 8, 2015 9:29 AM
    Wednesday, September 16, 2015 1:53 PM

All replies

  • Hi Piyush,

    According to your description, you need to display the value from different table based on the value on the column, right?

    I have tested it on my local environment, we can use LOOKUPVALUE function to achieve your requirement.
    LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…)
    It returns the value in result_columnName for the row that meets all criteria specified by search_columnName and search_value.

    In your scenario, the DAX expression looks like below.
    =IF(FactSales[Category]="CostPrice",
            LOOKUPVALUE(CostPrice[Cost],CostPrice[ProductID],FactSales[ProductID]),
            IF(FactSales[Category]="SalingPrice",
                        LOOKUPVALUE(SalingPrice[Sales],SalingPrice[ProductID],FactSales[ProductID]),
                        LOOKUPVALUE(SalingPrice[Sales],SalingPrice[ProductID],FactSales[ProductID])-LOOKUPVALUE(CostPrice[Cost],CostPrice[ProductID],FactSales[ProductID])
                 )
           )

    If this is not what you want, please elaborate your requirement, so that we can make further analysis.

    Regards,


    Charlie Liao
    TechNet Community Support

    Monday, September 14, 2015 9:28 AM
  • The suggestion above works for a calculated column, but not for a calculated field. This is because in a calculated field you cannot directly reference a column. Instead, it should be aggregated.

    I would add a Code column to your Table2 with values 1, 2 and 4 for CostPrice, SellingPrice and Profit, respectively. You can then create a calculated field like

    Result:=SWITCH(SUM(Table2[Code]),1,[CostValue],2,[SellingValue],4,[Profit])

    You should NOT have a relationship between Table1 and Table2 in this case. This would complicate matters because in [Profit] you need both the Table1 rows for cost value and for selling value.

    • Proposed as answer by Charlie Liao Tuesday, September 22, 2015 8:56 AM
    • Marked as answer by Charlie Liao Thursday, October 8, 2015 9:29 AM
    Tuesday, September 15, 2015 11:07 AM
    Answerer
  • Your simplest solution is to denormalize your data model, including a separate column for each of CostPrice, SellingPrice, and Profit. You can then use these with much simpler functions.

    For your specific use case, you can use your Category table as follows:
    Measure:=
    IF(
      HASONEVALUE(Table2[Category])
      ,SWITCH(
        VALUES(Table2[Category])
        ,"CostPrice", SUM(Table1[CostPrice])
        ,"SellingPrice", SUM(Table1[SellingPrice])
        ,"Profit", SUM(Table1[Profit])
      )
      ,BLANK()
    )
    



    GNet Group BI Consultant

    • Proposed as answer by Charlie Liao Tuesday, September 22, 2015 8:56 AM
    • Marked as answer by Charlie Liao Thursday, October 8, 2015 9:29 AM
    Wednesday, September 16, 2015 1:53 PM