SUMX formula showing average on row total instead of sum total

[365_PBI_GUY]

Hi,

I have the following formula:

Return_on_Sales% = iferror(sum('Table1'[Profit]) / sum('Table2'[Revenue]),"0%")

Which gives me the return on sales as a percentage for each row however for the row total on my matrix table, it's giving me an average, instead of the percentage of ros for all sales. 

I can't share specific data, but my total revenue is 200m and profit is 22m, so ROS would be around 11% but it's displaying 3.5% because it's taking an average of my rows instead of the grand total of profit divided by total revenue. 

Can anyone explain why this is and how I would rectify this? 

[Anonymous · Accepted solution]

Thanks for the reply from lbendlin and sjoerdvn.

Hi @365_PBI_GUY ,

If we look at the problem itself, total error is common. The totals are calculated independently of the context of the totaling row, you can try the following formula:

Measure2 =
IF (
    HASONEVALUE ( 'Revenue'[Revenue] ),
    DIVIDE ( SUM ( 'Profit'[Profit] ), SUM ( 'Revenue'[Revenue] ), 0 ),
    DIVIDE (
        SUMX ( ALL ( 'Profit' ), 'Profit'[Profit] ),
        SUMX ( ALL ( 'Revenue'[Revenue] ), 'Revenue'[Revenue] ),
        0
    )
)

I also found some link about incorrect total for you, which I hope will help you out:

Why Power BI totals might seem inaccurate - SQLBI

Dealing with Measure Totals - Microsoft Fabric Community

Best Regards,
Zhu
Community Support Team

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

[sjoerdvn]

You mention SUMX in the title, but there is no SUMX in your measure / formula ?

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...