DAX Measure with Nested IF Statements

[MWinter225]

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 Number	Adjustment	Sales
1	a	4
1	b	2
1	c	6
2	a	4
2	b	8
2	c	7

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 Number	Sales	Adjusted Sales
1	12	8.8
2	19	14.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

[Sean]

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

[MWinter225]

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 Number	Adjustment	Sales	Adjusted Sales
1	a	4	31
1	b	2	27.9
1	c	6	15.5
Total		12	74.4
2	a	4	31
2	b	8	27.9
2	c	7	15.5
Total		19	74.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 Number	Adjustment	Sales	Adjusted Sales
1	a	1	31
1	a	2	31
1	a	3	31

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]

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

[Sean · Accepted solution]

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

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!

[Adham]

Thank you sir, you saved me hours of chasing my own tail

[Mark_Timson]

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. 

[jubrna01]

This solved my problem, thanks  🙂

[Kayak6000]

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