Forum Discussion

MWinter225's avatar
MWinter225
Advocate IV
9 years ago
Solved

DAX Measure with Nested IF Statements

Hey Everyone,

 

This is something that is relatively easy for me to figure out in other BI tools but I've had some issues with Power BI and DAX. Here is a sample table "Sales" :

  

Store NumberAdjustmentSales
1a4
1b2
1c6
2a4
2b8
2c7

 

What I want to do is show total Sales value and an Adjusted Sales value based on an Adjustment Rule.

 

The Adjustment Rule is as follows:

IF Adjustment =a then 100% of sales

IF Adjustment=b then 90% of sales

IF Adjustment=c then 50% of sales

 

So I want to have a table that has the total sales and adjusted sales:

Store NumberSalesAdjusted Sales
1128.8
21914.7
   

(i'm pretty sure I got the math right)

 

I started off with a dax statement in a calculated column that is something like:

IF(Adjustment="a", SUM(Sales),

     IF(Adjustment="b", SUM(Sales)*0.9,

     IF(Adjustment="c", SUM(Sales)*0.5,0

)))

 

But when it populated in Power BI it doesn't make sense at the row level. I think it was duplicating the values over all the rows so when I went to sum up all the adjusted sales it was exponentially more than the sales--- which according to the calculation it should be the same or less than the sales.  I think I need a measure but I noticed that I can't have string columns in measure statments.  How can I do this in DAX?

 

Thanks in advance,

 

Matt

 

 

 

 

Need Help
  • Sean's avatar
    Sean
    9 years ago

    MWinter225If you do want Measures - these should work also! :smileyhappy:

    MEASURE 1

    Total Adj Sales ALT =
SUMX (
    'Table',
    IF (
        'Table'[Adjustment] = "b",
        'Table'[Sales] * 0.9,
        IF ( 'Table'[Adjustment] = "c", 'Table'[sales] * 0.5, 'Table'[Sales] )
    )
)

    MEASURE 2 - SWITCH is internally converted into nested IFs - one thing I really like is that its much easier to read and write

    Total Adj Sales ALT 2 =
SUMX (
    'Table',
    SWITCH (
        TRUE (),
        'Table'[Adjustment] = "b", 'Table'[Sales] * 0.9,
        'Table'[Adjustment] = "c", 'Table'[sales] * 0.5,
        'Table'[Sales]
    )
)

    Now you have 3 options which should all give you the same result!

     

    Good Luck! :smileyhappy:

8 Replies

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    MWinter225

    Create a Calculated COLUMN

    Adjusted Sales =
SWITCH (
    TRUE (),
    'Table'[Adjustment] = "a", 'Table'[Sales],
    'Table'[Adjustment] = "b", 'Table'[Sales] * 0.9,
    'Table'[Adjustment] = "c", 'Table'[Sales] * 0.5
)

    Then just sum it like do the Sales column.

    Good Luck! :smileyhappy:

     

    • MWinter225's avatar
      MWinter225
      Advocate IV
      9 years ago

      Hey Sean! Thanks for replying!

      This SHOULD be the answer and I don't get any errors, however; when I enter what you suggested I get this as a result:

       

       

      Store NumberAdjustmentSales Adjusted Sales
      1a431
      1b227.9
      1c615.5
      Total 1274.4
      2a431
      2b827.9
      2c715.5
      Total 1974.4

       

      What I did was this:

       

      Adjusted Sales =
SWITCH (
    TRUE (),
    'Table'[Adjustment] = "a", SUM('Table'[Sales]),
    'Table'[Adjustment] = "b", SUM('Table'[Sales]) * 0.9,
    'Table'[Adjustment] = "c", SUM('Table'[Sales]) * 0.5
)

      It's the same as what you posted but I added a SUM in front of it because without it, it was giving me the error:

      "A single value for column 'Sales' in table 'Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

       

      So my real data has multiple "store numbers" and multiple "Adjustments" and several records of each. What it looks like when I look at the row level data is that the values are being duplicated over every row. For example:

       

      Store NumberAdjustmentSalesAdjusted Sales
      1a131
      1a231
      1a331



      Say the sales here was correct, but the Adjusted sales here is the TOTAL SUM of ALL SALES DATA POINTS for the piece of the calculation " 'Table'[Adjustment] = "a", 'Table'[Sales], "  Does that make sense? So when I average the Adjusted Sales (like in the example above) I get the sum of all the sales data points in the Sales column (31) when I sum the Adjusted sales and I have 50 rows of data I get 31*50 =1,550 for the Adjusted Sales in Store Number '1' in Adjustment 'a'.

       

      Any suggestions or clarifications? hopefully I'm being clear enough!

       

      thanks,

      Matt

      • Sean's avatar
        Sean
        Community Champion
        9 years ago

        MWinter225

        I suggested above that you create a Calculated COLUMN first not a Measure!

        The error you are getting indicates you are creating a Measure.

        My Column formula above would be evaluated on each row so you don't need a SUM there.

        After you've created this Column then create these 2 simple Measures say...

        Total Adjusted Sales = SUM ( Table[Adjusted Sales] ) 

        Total Sales = SUM ( Table[Sales] )

        Then create your Table Visualization drag Store Number and these 2 Measures and you'll get the result you want!

        Good Luck! Hope this makes sense! :smileyhappy: