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:
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 :)
Thank you sir, you saved me hours of chasing my own tail
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
