Table Column Total Incorrect

[nickelcap]

Hello,

I've encountered an issue totalling a table column in Power BI. I have two columns, a category column and a count column. I want to have the sum of the count column below the table, but it comes up incorrectly. Here's a capture of my table at one time:

Example 1

And here it is later:

Example 2

As you can see, if the total were just a sum of the 'Count of Name' column it'd be returning 32 rather than 28 in the first example, and 29 instead of 28 in the second example. For a bit more information, the table has a filter applied to it such that only dates within the past half day are counted. The individual values under the 'Count of Name' column are correct. When I try just having a card for 'Count of Name' with the date/time filter applied, I get 28 as well. So there's something about the number 28 that Power BI is stuck on.

The way the time filter works is by creating a calculated column with the following equation:

Last Half Day = IF(AND('SignalData'[TYPE]="Poll",'SignalData'[DATE_TIME]>[Past Half Day]),"Past Half Day","")

Past Half Day is a measure, with the simple formula: 

Past Half Day = NOW()-(1/2)

The 'Count of Name' column is generated by entering the 'Name' column in my dataset to the Values field and selecting 'Count'. What's really throwing me off is that the individual counts are all correct, it's just the total that is off somehow. The fact that it's reporting 28 consistently tells me that it must be totalling something other than the Count of Name, but I'm not sure what that something is.

Any help or guidance you all can provide would be greatly appreciated.

Regards,

Dan

[kcantor]

@nickelcap

It is not you. In PowerBI and  Powerpivot, the calculations iterate for each cell so sometimes they display unexpected answers in subtotals and grand totals.  It is a difficult concept to explain so I will just leave this blog post here to help you. Explaining it is above my paygrade . . . 🙂

http://www.powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/

[nickelcap]

Thanks for the link. I think I have something of an understanding of what's happening after, I'm just not sure that the solution offered in that post helps me solve the problem. I'll have to work at it.

Thanks,

Dan 

[kcantor]

@nickelcap I would be happy to try an assist further but I would need some idea of what your data looks like. Can you post a sample? Unfortunately, the small amount in your question does not really give a clear picture of what the problem could be.

[nickelcap]

Thanks for the offer, @kcantor. Here's a snippet of what my data looks like, along with the formula used to calculate the last half day:

Data Sample

From that sample, in the table visual 'Name' is counted using the 'Count (Distinct)' option and BASE is set as 'Don't summarize'. I then bring the 'Last Half Day' column to 'Visual level filters' and filter to only 'Past Half Day'. Currently, I'm getting something that looks like this:

Current Table

The total I'm looking for would be 27, not 26.

Thanks,

Dan

[Vvelarde]

@nickelcap

Maybe is because you have equals names in different Base.

Example

Base Name

 A     John

B      John

Distinct Count Result

Base     Count

A          1

B          1

Total    1

[nickelcap]

@Vvelarde I had wondered about that, but even with the standard count the total still is off. Would the duplicate names still throw off the total with the standard count?

[Habib]

Hi @nickelcap,

I will agree with @kcantor regarding the complexity of total calculation on Power BI and Power Pivot. For better explanation of your problem can you please share more details regarding what COUNT function you are using. Is this Count Distinct or simple count. Like in case Count Distinct, If 2 Bases has same names, then talbe will show 1 against each base and total will be 1 as well because total will be regardless of Base.