Forum Discussion
DAX Measure with Nested IF Statements
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:
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:
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
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:
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:
This solved my problem, thanks :)
