Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Table grand total of calculated measure column no longer working

Hello PBI Community, 

 

Having a frustrating issue with my PBI file. I completed the weekly file refresh and now a couple of the calculated measures in one of my report tables is no longer totaling correctly. 

I have been updating this file on a weekly basis since early March 2019 without fail and now suddenly a couple of the table columns are no longer totaling correctly. 

 

Remediation Steps I have tried:

 

- compared against the source file and the grand total in power bi is definitely incorrect for the two columns. 

- checked visual, page, and report level filters

- recreated the measure 

- reloaded, refreshed 

- having massive problems refreshing in desktop and online, i have only been able to refresh successfully one time over the past 6 hours of trying. keep getting errors like EOF 0 bytes, ODEB error, etc. 

 

The two problem columns are around calculating total current headcount and total current pay

 

Example of the calculated measure for total current headcount:

 

CF_Current Total Active Headcount =
VAR _Date1 = LASTDATE('1 Actives_Embedded'[Report Effective Date])
VAR _HeadCountDate1 =
CALCULATE (
DISTINCTCOUNT ( '1 Actives_Embedded'[Employee ID] ),FILTER(ALL('1 Actives_Embedded'[Report Effective Date]),'1 Actives_Embedded'[Report Effective Date]=_Date1))
RETURN
_HeadCountDate1

 

Screenshot of the table (note the line items are correct, it is just the grand total on "Most Recent Active Headcount" in the table that is not correct, as well as the calculated difference "Headcount Change" does not align with the actual line items):

 

Capture.PNG

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

The issue was due to a category name change that was causing the table to drop the data. 

View solution in original post

6 REPLIES 6

Hi @Anonymous 

Do you have a sample file?

 

 

 

Anonymous
Not applicable

I cannot provide an actual sample file. I have an "Actives" file that contain millions of records and columns. The file gets appended every week. The basic info the calculation is using is Report Effective Date and Employee ID. For example:

Report Effective DateEmployee ID
11/7/201912044
11/7/201912045
11/14/201912044
11/14/201912057
11/14/201912061
Anonymous
Not applicable

I believe we have identified the issue. Will report back when confirmed. 

Anonymous
Not applicable

The issue was due to a category name change that was causing the table to drop the data. 

dax
Community Support
Community Support

Hi amy_mcmasters,

It seems that you solved this problem, right? If so and if you'd like to, you could mark corresponding post as answer or share your solutions. That way, people who in this forum and have similar issue will benefit from it.

Best Regards,
Zoe Zhi

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

 

That's great! you may close this thread and accept this as solution for future references.

 

Thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.