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