Issue with Sum differrent when summing revenue and time it by average price

[mchacha]

Hi,

I have an issue with my sum measure and I don't know how to fix it... To make it short, I simplified my tables and data : 

This is my visuals : 

I want to show one table with my actuals data, with a summary per campus, how many students are invoiced (calculated with a count of invoices), the revenue (the sum of invoice amount) and the average price (calculated with revenue / student numbers). The total works for this visual. 

My issue remains on my second table, with the budget. I want to show the same things with budget data. In my budget table, I only have student numbers for the campus. So to calculate revenue, I do revenue = Budget student number * actual average price. This works per campus, the issue is in the total. In the total, it does total student number * actual average price total, while it should do a sum of the total revenue. 

Here's my tables (simplified a lot) : 

I have many lines (thousands of thousands), and many filters on my visuals on common qualitative information (year, period, type of program and so on). 

I understand the logic of the issue but I don't know how to fix the total, I want to do a budget total revenue = total of each campus revenue, but it implies other filter (such as stated above)... 

Thanks a lot for your help.

Marie

[Anonymous · Accepted solution]

Hi @mchacha ,

May be you can try formula like below:

Students Actuals = DISTINCTCOUNT('Actuals'[Invoice Number])

Revenue Actuals = SUM('Actuals'[Invoice Amount])

AVP Actuals = DIVIDE([Revenue Actuals], [Students Actuals], 0)

Revenue Budget = 
    SUMX(
        'Budget',
        'Budget'[Student Number] * [AVP Actuals]
    )

Best Regards,
Adamk Kong

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

[mchacha]

thanks, it worked 🙂