Forum Discussion
Totals wrong
Hey there, here's the link to the file.
I've been trying to compute the number of purchases stored in a fact table. Here's the measure that I've authored so far:
Purchases =
CALCULATE (
COUNTROWS ( f_Vendas ),
CROSSFILTER ( f_Vendas[IdCompra], f_Vendas_Adicional[IdVenda], Both )
)
Here's the diagram model:
First problem: the totals (6 and 3, that should be 3 and 2)
As shown in the above image, the total seem to be correct, however what I aim to do is to show the total of daily purchases, and therefore the total should be 3 instead of 6 (there were only three purchases on Sep 1st, as stored in f_Purchases).
Second problem: the individual numbers
On Sep 2nd, the dark blue color representing “Mixirica” should be 3, not two, as three purchases were made in the day.
I've tried using SELECTEDVALUE() within an IF statement, but didn't manage to get the expected result.
Any thoughts on how to compute it correctly?
Thanks in advance.
4 Replies
Anonymous First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8Hello Anonymous
it seems like total on bar chart you is summing the values rather than calculating the total base on the dax code.
cz if you notice in the table , the total for 1/9 is correct ( = 3 ) , however in the bar chart, the total is 6 which is = 1 + 2 + 3 .
i can think of an alternative is to create a measure per each product : and create a dummy measure and a total purchases per day as below:
dummy = CALCULATE( DISTINCTCOUNT(f_Purchases_Aditional[IdPurchase]), REMOVEFILTERS(d_Products[Product] ))/3Purchases total = CALCULATE( DISTINCTCOUNT(f_Purchases_Aditional[IdPurchase]), REMOVEFILTERS(d_Products[Product] ))then you add the 4 measures as above.
finally, go to format visual, choose dummy variable, and set the value --> field = purchases total measure
change background color and value color .
you will get the following :
NB: to remove the legend "dummy" , just change the name of the measure in the visual to " "
let me know if this helps .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
