Forum Discussion
Total Avarage Wrong
Sorry for the late reply.
The avarages are still not working, but your simplified model made me realized why. I have not checked whether IDReceipt are doubled across restaurants. Since they are, Power BI achieves a different sum of DistinctCount(IdReceipt) if it calculates this value across all restaurants at once than when it would just add Distinct ID Receipts from A, B and C individually.
So thanks, I understand the problem now and have to sort it out with the client. Solution accepted.
Regards,
JFW
Community SupportHi JFV ,
You can change the Revenue using DAX below.
Revenue = CALCULATE(SUM(Revenue[NetValue]),Revenue[If_P&M]="YES")
Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EaRJjuaV75xKsbiSiqYRPvsBqdoKiiVfuMhWqLayX0king?e=Hx9RYU
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the reply!
However, I could not use this solution as I do need to take full Revenue into account, not just P&M (P&M is just a abbreviation for main courses - we only count visitors as those who order it, but in calculating avarage revenue per X we still use all orders). Regardless, I did enter revenue formula as you suggested to check if it works, and sadly it did not help - the total sum still seemed unrelated to the avarages of singular restaurants.
Plus, in Your file if you change the formula of Revenue so it is just sum of Net Value, and swith off the "P&M" filter the total sum is still correct. Can the problem lay elsewhere?
Regards,
JFV
- v-xicai
Hi JFV ,
I am not sure what output is your demand? Could you please share your sample data or desired output screenshots for further analysis?
I changed the Number of Visits like DAX below ,the Total return the result which have considerated the all IdReceipt .
Number of Visits = DISTINCTCOUNT(Revenue[IdReceipt])
Best Regards,
Amy
- v-xicai
Hi JFV ,
Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case.
Best regards
Amy Cai
Sorry for the late reply.
The avarages are still not working, but your simplified model made me realized why. I have not checked whether IDReceipt are doubled across restaurants. Since they are, Power BI achieves a different sum of DistinctCount(IdReceipt) if it calculates this value across all restaurants at once than when it would just add Distinct ID Receipts from A, B and C individually.
So thanks, I understand the problem now and have to sort it out with the client. Solution accepted.
Regards,
JFW