Calculations are not adding up correctly in Power Bi Table

[ebc124]

Hi! I am a beginner to Power BI and I am having an issue with a table that I created where specific "cells" or fields are not calculating correctly and others are.  I have provided a snapshot of the table I created. All values within the table are correct except the values in red boxes. For the year 2021 sum of usage, count of locationNo and count of accountNo are adding up correctly, however not for sum of charges. For the total, the total distinct count of account numbers are not adding up correctly and neither are the total distinct counts of location number, it seems the table is simply taking the 2022 data for the total.

Why is this happening? What can I do?

[Wilson_ · Accepted solution]

Hello ebc,

Technically, they are all calculating correctly; whether they are all calculating as you intended is a different story altogether. 🙂

That being said, given that you've provided literally no information about your table, it's hard to know why you think the numbers in red boxes are wrong and what you're expecting instead.

My hypothesis for your confusion about the distinct counts of location and account though is you think the Total row is supposed to be a summation of all the other rows in the matrix. It's not. What it's doing is calculating a distinct count of your data table without filtering for any fiscal year. The total being the same as the distinct counts for 2022 tells me there are no location numbers or account numbers that appear between fiscal year 2012 and 2021 that isn't also in 2022.

[ebc124]

Hi Wilson,

Thank you so much for your response. To provide more detail about my table, I am using one of the data sources I loaded into Power BI. I loaded the following attributes of my dataset as follows:

So in the table I sent in my question the fiscal year, usage, charges, accountno, and locationno are all from the PatronageAR dataset.
The Sum of Usage, Sum of Charges, Count of AccountNo, Count of LocationNo are all the built-in Power BI measures.

What I would like to acheive with this table is for the sum of usage to calculate the sum of all usage amounts for each year and total these amounts (sum each row) for the total, the same is desired for the sum of charges.

for the count of accountno and count of locationno, your hypothesis is right that I would like for distinct account numbers and location numbers to be counted for each year and for the total to display the sum of each row. 

Please let me know if there is any information I may be missing. 

[Wilson_]

Hello ebc,

This SQLBI video should do an excellent job of explaining why the total row is not simply a summation of the rows that appear in your table/matrix. It's a very easy mistake to make for beginners to Power BI so you're certainly not alone. 😄 That being said, it is possible to get it to do what you want for the two discount count measures.

The location column would look something like this (essentially by turning an exercise of counting into one of summation):

Sum of Location Count = 
VAR SummaryTable =
SUMMARIZE (
        PatronageAR,
        PatronageAR[Fiscal Year],
        "Distinct Count Location", DISTINCTCOUNT ( PatronageAR[LocationNo] )
)

RETURN
SUMX (
    SummaryTable,
    [Distinct Count Location]
)

Still no idea why you believe Sum of Charges is incorrect. Given what you've shared, I see no reason why it would be.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[ebc124]

Wilson, 

Thank you so much this worked for both of the distinct counts and is calculating as I intended! I now know why as well this was very helpful and a great video resource. I appreciate this help.

From my understanding PowerBI is calculating the sum of charges correctly, however I am thinking I have set something up such that it is double counting the values for the sum of charges in the year 2021. The reason I am thinking this is becuase the source file for PatronageAR consists of multiple sheets with data for each year. There are charges for each account for the year 2011, 2012, and so on. When I go into the source file for 2021 and perform a sum of all the charges the total I get is $31,892,972.80 which is exactly half of $63,785,945.60 (which is displaying in the table). This is the only value that is being doubled, in the table, the sum of usage is displaying the same as the source file in the year 2021 as well as the distinct count of accounts and distinct count of locations. I am not sure what may be causing this to happen. Might you have any ideas?

[Wilson_ · Accepted solution]

ebc,

Do you have subtotal rows or a grand total row in the 2021 source that is being picked up

in Power BI?

(Glad the SQLBI video helped. They're my go to for Power BI learning.)

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[ebc124]

I am not sure how to check for this, I have totals set to on.

[Wilson_]

ebc,

I mean if you look in your source data, in Excel or whatever, are there subtotal rows or a total row that are being picked up as a row of "normal" data in Power BI in the table?

For example, something like the below, where the "a Total" row is being picked up and doubling the total:

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[ebc124]

I just checked for this and this is not happening. There are no calculations in the source file. I also searched for the value and it is not there.

[Wilson_]

ebc,

I can't help you any further unless you share your pbix.

[ebc124]

Hi Wilson,

Please excuse me for responding so late. I ended up holding off on this project. I am not sure what the best way to send the file to you is since it is a large file.

Thank you

[Wilson_]

Hi ebc,

You can upload it to a fileshare service (ex: Google drive, Dropbox, etc.) and share the link.

[ebc124]

Hi Wilson,

I discovered that there was actually a totals row in the table of my source file and that was causing the issue.

Thank you so much for you help!

[Wilson_]

Sweet, glad you figured it out. That tends to be what I look for first if I'm seeing double (grand total) or triple (grand total and subtotal) what I'm expecting. 🙂

[Wilson_ · Accepted solution]

Hello ebc,

Technically, they are all calculating correctly; whether they are all calculating as you intended is a different story altogether. 🙂

That being said, given that you've provided literally no information about your table, it's hard to know why you think the numbers in red boxes are wrong and what you're expecting instead.

My hypothesis for your confusion about the distinct counts of location and account though is you think the Total row is supposed to be a summation of all the other rows in the matrix. It's not. What it's doing is calculating a distinct count of your data table without filtering for any fiscal year. The total being the same as the distinct counts for 2022 tells me there are no location numbers or account numbers that appear between fiscal year 2012 and 2021 that isn't also in 2022.