Total Avarage Wrong

[JFV]

Hi,

I created some simple pivot tables consisting of e.g. Revenue, Number of Visit and Avarage Revenue per Visitor based on a data collected by a restaurant chain. While all the measures work fine by themselves, the total avarage for Avarage Revenue per Visit across all seleced restaurants is higher than the avarages for any singular restaurant which is unexpected:

I have the data for all the orders made by customers in any of the restaurants. I calculate the Avarage Revenue per Visit using following steps:

1) Revenue = sum(Revenue[NetValue])

2) Number of Visits = CALCULATE(DISTINCTCOUNT(Revenue[IdReceipt]); Revenue[If_P&M] = "YES") , that is I filter how many receipts have a P&M position on them and only count those.

3) Avg Rev per Visit = [Revenue] / [Number of Visits]

It's just an example as I experience this issue in several of calculated measures I use. I read that it is better to use SumX if my totals are wrong or filter data with HASONEVALUE. Unfortunately I lack PBI knowledge to make those solutions work in my model. Could someone help me out?

Unfortunately, I will not receive a permission to post Queries or PBI file online.

Regards,

JFV

[v-xicai]

Hi @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/EaRJjuaV75xKsbiSiq...

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JFV]

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 ,

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

[JFV · Accepted solution]

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

[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