Total Incorrect

[Kurt4597]

Hi all,

Currently experiencing an issue that I have hit a wall on. Would really appeciate it if anyone could help me with a solution

I have the following Table, each of these columns has been pulled in from a different table.
MTD final sales = 

'MTD Forecast'[FinalSales] + 'MTD Forecast'[MTDsales Except Last week]

CPA = Pulled from a seperate static table 

MTD Revenue = 

MTD Revenue = 'MTD Forecast'[MTD Final Sales] * average(CPA[CPA])

(Average was used for CPA here as the first table is grouped by provider name)

The issue is the total for the MTD Revenue is incorrect. I would like it to be £10,050 + £1,768 + £128 = £11,946. However, PowerBI is calculating £12,799 which I believe is the total sales * an average of the CPA.

Any suggestions / advice would be much appreciated. 

Thanks All

[v-yiruan-msft]

Hi @Kurt4597 ,

Please try to create another new measure as below and put it on the visual to replace the original measure [MTD Revenue] . Later check if you can get the correct total values...

Measure = SUMX ( VALUES ( 'MTD Forecast'[Provider Name] ), [MTD Revenue] )

Incorrect total values fix methods:

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

If the above one can't help you get the desired result, please provide some sample data in your tables 'MTD Forecast' and 'CPA' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Kurt4597]

Thank you for the response yingyinr, unforutatenly the measure you suggested does not work. I Have attatched a sample file to better explain the issue. Many Thanks! https://www.dropbox.com/s/95pgkz6k1np28qr/Sample%20File.pbix?dl=0

[v-yiruan-msft · Accepted solution]

Hi @Kurt4597 ,

I updated your sample pbix file(see attachment), please check if that is what you want. Please create another new measure as below to replace the measure [Revenue] on the visual...

Measure = SUMX ( VALUES ( 'Master Filter'[Provider Name] ), [Revenue] )

Best Regards

[Kurt4597]

Any help would be greatly appreciated