cancel
Showing results for
Did you mean:

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

)))

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?

Matt

1 ACCEPTED SOLUTION
Community Champion

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

MEASURE 1

```Total Adj Sales ALT =
SUMX (
'Table',
IF (
'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!

8 REPLIES 8
Community Champion

@MWinter225

Create a Calculated COLUMN

```Adjusted Sales =
SWITCH (
TRUE (),
'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!

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

```Adjusted Sales =
SWITCH (
TRUE (),
'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

Community Champion

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

Community Champion

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

MEASURE 1

```Total Adj Sales ALT =
SUMX (
'Table',
IF (
'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!

Helper III

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

Helper I

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.

Frequent Visitor

This solved my problem, thanks  🙂

Frequent Visitor

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

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors