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
Solved! Go to 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!
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!
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!
@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!
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
This solved my problem, thanks
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.