cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MWinter225 Regular Visitor
Regular Visitor

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

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: DAX Measure with Nested IF Statements

@MWinter225If you do want Measures - these should work also! Smiley Happy

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! Smiley Happy

7 REPLIES 7
Super User
Super User

Re: DAX Measure with Nested IF Statements

@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! Smiley Happy

 

2017-01-13 - SWITCH.png

MWinter225 Regular Visitor
Regular Visitor

Re: DAX Measure with Nested IF Statements

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

Super User
Super User

Re: DAX Measure with Nested IF Statements

@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! Smiley Happy

Super User
Super User

Re: DAX Measure with Nested IF Statements

@MWinter225If you do want Measures - these should work also! Smiley Happy

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! Smiley Happy

Kayak6000 Visitor
Visitor

Re: DAX Measure with Nested IF Statements

hello all,

that is really useful but i have encountered a further issues...

 

tried the switch function and I managed to get it working or at least syntactically correct, but the figures were calculating incorrectly.

 

So I tried the If function as per Option 2   and again all works but , although correctly calculating at a 'base' level, what it is doing is adding the percentages upwards (if that makes sense)

as the calc moves ‘up’ the visual to a higher level view,  it starts adding percentages giving me the total of 526.94% when it should be 105.53%

 

HN

1876246.84

-1785153.62

 

25.26%

105.10%

SH

658942.06

-631713.39

 

8.87%

104.31%

SH

1924556.23

-1824569.79

 

25.91%

105.48%

FN

1793206.98

-1685529.97

 

24.14%

106.39%

SF

1585047.95

-1500208.79

 

21.34%

105.66%

 

7838000.06

-7427175.56

 

105.53%

526.94%

 

 

 

 

 

 

 

 

I have looked the default summarisation and that is set to “don’t summarize” , in the visual itself when I right clicked, this is set as “show value as’ > “no calculation”

 

in essenec it shoud be adding up column 2 , adding up column 3 and then dixiding column 2 / column 3  the sums i have shown i  bold at the bottom (ps although negative I multiply the result by -1 to give me the positive % so ignore the negatives) and in fact it is because of these negatives that i needed the Switch or If  functions.

 

So am at a bit of a dead end… any ideas ?

 

thanks peeps

 

jubrna01 Frequent Visitor
Frequent Visitor

Re: DAX Measure with Nested IF Statements

This solved my problem, thanks  Smiley Happy

Mark_Timson Frequent Visitor
Frequent Visitor

Re: DAX Measure with Nested IF Statements

Hi Is there anyway to do this with more than one table and column? 

 

I have 

 

IF Table.Col = "Y" THEN SUM(Table1.Col) ELSE IF Table.Col = "N" THEN SUM(Table2.Col) ELSE 0.

 

I have tried to keep the above simple.