What to Do When Power BI Totals are Wrong

Have you ever gotten yourself in a situation when your calculation works great on a row by row basis, but when you look at a total, it does not match to the SUM of the individual rows?

If so, the problem is not Power BI, the problem is in your calc. The good news is that it’s usually very simple to fix this issue. I go over a possible way to fix it in the video below:

Here is the data set for the video:

Date

Customer

Sales

Margin

Rep

1/1/20

A

100

10

John

1/1/20

B

200

50

Tom

Here are all the calculations that I used in the video:

Sales = SUM(Sales[Sales])

Margin = SUM(Sales[Margin])

Margin % = DIVIDE([Margin], [Sales])

Commission = IF([Margin %]>.15, .10*[Sales], .05*[Sales])

Fixed Commision = SUMX(VALUES(Sales[Rep]), [Commission])

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s