Power BI: Totals Incorrect
A recurring problem that Power BI users find is that of incorrect totals.
Here is an example: I have a table with Salespeople and their monthly sales, based on that I want to calculate their sales incentive. The sales incentive is equal to 5% of the sales if they sell more than 80k$, otherwise it's zero.
Here is the incentive measure formula:
Incentive = IF(SUM([Sales])>80000, SUM([Sales])*5/100,0)
And here is the result:
The total is wrong: Power BI applies the incentive formula on the total instead of adding up the incentives of the salespeople.
First solution for incorrect totals in Power BI: move from a calculated measure to a calculated column
The easiest way to fix incorrect totals is to create a calculated column instead of a calculated measure. Calculated columns do exactly what we want: calculate the incentive on each row, and then sum up the rows for the total.
On the Modeling tab, choose New Column
Here is the formula to use.
Incentive column = IF([Sales]>80000, [Sales]*5/100,0)
It's the same formula as before, with the exception of SUM: a calculated column is evaluated on each row and it does not need the SUM function.
Here is the result, the incentive total is now correctly calculated!
Second solution: create a summarised table and use SUMX
What if the solution above is not applicable to you? For example, you have the following initial table:
And the same incentive column formula as before
Incentive column = IF([Sales]>80000, [Sales]*5/100,0)
The incentive column is now wrong: it's checking whether the threshold of 80k$ is met on each product. The threshold should be checked on the total sales of each salesperson, not by product.
The solution is to:
1) Create a summary table by salesperson, removing the product detail
2) Calculate the incentive for each row of this new table
3) Add up the incentives to calculate the total
Here is the formula for that:
Incentive measure correct =
var summarizedTable = ADDCOLUMNS (
SUMMARIZE ( 'Sales', 'Sales'[Salesperson] ),
"Sales", CALCULATE ( SUM('Sales'[Sales]) ))
RETURN
SUMX(summarizedTable,
IF([Sales]>80000, [Sales]*5/100,0))
The variable 'summarizedTable' takes care of step 1 of the solution, and gives us the summary table we need.
Specifically, SUMMARIZE takes two parameters:
– the table to summarize (Sales),
– the columns to keep (only Salesperson and not Product)
ADDCOLUMNS takes three parameters:
– the table created by SUMMARIZE
– the name of a new column to add to the summarized table (Sales)
– the formula for the new sales column (the sum of sales from the initial table)
Afterward, SUMX takes care of steps 2 and 3 of the solution: it calculates the incentive for each row of the summary table and then adds it up.
Here is the new final result with the correct incentive:
I hope this was clear, and do not hesitate to ask questions on this post!
Enjoyed the article? Subscribe to the newsletter and download the Power BI of the article for free.
13 Comments
Tnanx!
I am glad this helped you!
Hi! I'm newbie in powerBi. Could you help me with solving one problem?
Hi. Yes, send me an email to lc@finance-bi.com
Thanx a lot for your decision! It is simple and brilliant at the same time!
Hi Luca – I am kind of struck in below which is something similar.
1- I have month wise product wise , quantity and price.
2- I want to calculate amount which is quantity*price.
3- My price is column data already available with me in dataset.
4- My quantity field is calculated measure having logic of opening and closing quantity.
5-How do I get amount for each product correct as I cannot multiply column with measure?
6- My results should not have total for price.
7- My amount total should be sum of individual amount calculated in step 5.
Is there a way to achieve above?
Thanks.
Sanjay.
Hi Sanjay,
thank you for your message.
You can accomplish the calculation of the amount using SUMX.
The formula would look like this:
Amount measure = SUMX('Sales Table', CALCULATE([Quantity Measure]) * [Price]) )
What this formula does is:
1- Go through each row of the sales table
2- For each row of the sales table multiply the quantity measure (which integrates the logic of opening and closing stock) by the price
3- Sum up all the rows
Regarding the price and the fact that it should not be added up, I would recommend recalculating the price as a measure (even if it already exists in a table).
The formula for the price would be:
Price measure = [Amount measure] / [Quantity measure]
Does this help you?
Cheers
Luca
Hi all, Im actually facing an issue where I have a list of hierarchy.
Country>Customer>Department>Product
My measure is a switch function where if the slicer selected is sales, it will show sales amount, if cost, it will show cost amount.
However, i need to see top 20/bottom 20 by customer. the hierarchy should also only reflect on the top 20/bottom 20 customer.
for example from the top 20 customer, we will know these top 20 customer is coming from which country, what department they have what product they have..
is there a dax for this that i can refer to?
Hi there,
Just one note from my side: Use ADDCOLUMNS for expressions you want to calculate on your grouped table when using SUMMARIZE. Otherwise you run into the risk of having wrong result depending on your model. That did happen to me. Also performance is way better when using ADDCOLUMNS with SUMMARIZE.
Otherwise, great article. I hope it helps more people like it helped me.
Hi Nico,
thank you for your comment, and very good point for the ADDCOLUMNS.
I updated the article so future readers can benefit from your comment,
Luca
PowerBI beginner here. I have 2 questions.
You mention that summarize uses 4 parameters, but you are only stating 2 in the example. How come?
Could SUMMARIZE() take USERELATIONSHIP() as a second argument (the grouping by column)?
Hi Vic,
thank you for your comment.
You are right, in my example SUMMARIZE takes 2 parameters and not 4.
I have recently updated the article to use SUMMARIZE in combination with ADDCOLUMNS and I had not updated the description.
I now updated the text, please let me know if it's clear.
Regarding using USERRELATIONSHIP inside a SUMMARIZE, I have not tried that in the past.
If you tried it for your case, did it give you the result you were expecting?
I hope the above helps, and do not hesitate to share further comments,
Luca
this saved my day, thanks!