March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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!
Thank you sir, you saved me hours of chasing my own tail
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.
This solved my problem, thanks 🙂
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
148 | |
92 | |
72 | |
58 |