Totals wrong

[Pedro503]

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.

[Daniel29195]

Hello @Pedro503 

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]
     ))/3

Purchases 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! 🤠

[Pedro503]

Hey, Daniel. Thanks for your response.

In my real life file there are more than 30 different categories, hence it would not be the best scenario creating 30+ measures and using it as legend.

However, I appreciate your response and will try to do something in those lines.

Thanks, man.

[Greg_Deckler]

@Pedro503 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/n4TYhF2ARe8

[Pedro503]

Thanks for your response. I've voted for the idea. 

Also, before posting it here I tried to compute the measure as you suggested, but didn't work. However, appreciate it.