Table Visual not Matching Calculated Visual

[muom]

I have a visual that shows a percentage, calculated with this formula

Next to it, I have a table visual that (in theory) should match the percentage. But it doesn't. The Increased + Maintained in the table should equal the 52.1% on the larger visual.

I used these elements to make the table:

Earned.Change Status = 'PDE_EE-EnrollmentsTbl'[Earned Income.Change Status]

Count Adults = CALCULATE(COUNT('Exit Details_UDE'[Entry Exit ID]), AgeCatTbl[Adult-Child]="Adult")

% = CALCULATE(COUNT('Exit Details_UDE'[Entry Exit ID]), AgeCatTbl[Adult-Child]="Adult") shown as a percentage.

Where am I going wrong here? My table calcs are pulled directly from the same elements used for the big  number calculation.

[muom · Accepted solution]

Thank you everyone for the feedback. I attempted the various formulas suggested but was still finding the same issue with mismatch. There are no duplicate values when I checked and numbers didn't change when using DISTINCTCOUNT. In the end, I went into the query and merged the 'PDE_EE-EnrollmentsTbl'[Earned Income.Change Status] column into the Exit Details_UDE table, matching with Entry Exit IDs. That solved the issue!

Greatly appreciate all the various perspectives that helped me reach this solution.

[Deepaky1813]

I think the issue is that the table and the card are not actually calculating the same thing.

In your table, the % measure is only returning the count of adult records in the current row context. Since the table already has Earned Income. Change Status on the rows, each row is already filtered to one status. So that measure is not really calculating a percentage of total adults by itself.

To make the table match the card, you need to divide each row count by the total adult count.

I’d also check whether Entry Exit ID is unique. If it isn’t, COUNT() can overcount, and that can cause the row values not to reconcile with the total. In many cases DISTINCTCOUNT() is the better option.

A cleaner approach would be to create a total adult measure like:
Adult Exits = CALCULATE( DISTINCTCOUNT('Exit Details_UDE'[Entry Exit ID]), AgeCatTbl[Adult-Child] = "Adult" )

Then use a percentage measure for the table like:
Adult Exit % by Status = DIVIDE( CALCULATE( DISTINCTCOUNT('Exit Details_UDE'[Entry Exit ID]), AgeCatTbl[Adult-Child] = "Adult" ),                                          CALCULATE( [Adult Exits], REMOVEFILTERS('PDE_EE-EnrollmentsTbl'[Earned Income.Change Status]) ) )

And for the card:
Earned Income Status % = DIVIDE( CALCULATE( DISTINCTCOUNT('Exit Details_UDE'[Entry Exit ID]), AgeCatTbl[Adult-Child] = "Adult", 'PDE_EE-EnrollmentsTbl'[Earned Income.Change Status] IN { "Increased", "Maintained" } ), [Adult Exits] )

So the mismatch is most likely coming from one or more of these:

1. The table measure is not actually calculating a percentage
2. The table and card are being evaluated under different filter context
3. COUNT() may be double-counting if Entry Exit ID is not unique

If the row totals add up to more than the overall total, I’d definitely check for duplicate Entry Exit IDs or cases where the same ID appears under more than one status.

[jbarrosPT]

Deepaky1813 nailed the mechanism (row-context %, and DISTINCTCOUNT over COUNT — both real here). One thing worth adding, because it's probably the number that's actually confusing you:

Increased 30.31% + Maintained 33.58% = 63.89% in your table — but the card says 52.1%. Those aren't two versions of one number; they're answering two different questions. The card filters the numerator to Increased OR Maintained but divides by AllExits (all 551, "Stayed at 0" included). Your table rows divide the same way but per single status. So 63.89% is "% of all exits that improved or held", and 52.1% is a different denominator somewhere — most likely AllExits and your table's total aren't the same population.

Quickest way to see it: drop [AllExits] and your row COUNT side by side in the table as plain values (no %). If AllExits ≠ 551, you've found it — the card is counting exits your table isn't (or vice-versa), and it's almost certainly the COUNT-over-two-tables grain Deepaky flagged. Fix the denominator to one agreed population and both visuals collapse to the same 63.89%.

[Kedar_Pande]

@muom 

Your card measure counts distinct Entry Exit IDs where status is Increased OR Maintained in one pass. Your table's Count Adults measure has no filter on Earned.Change Status at all - it relies purely on row context from the visual. If there are duplicate/multiple enrollment records per Entry Exit ID in 'PDE_EE-EnrollmentsTbl', an ID can land in multiple rows of the table (counted twice) while only being counted once in the OR-based card measure.

Check for duplicate Entry Exit IDs per status in 'PDE_EE-EnrollmentsTbl' - that's almost certainly your mismatch.

[Rupa01]

Hi @muom,

Checkout below solution - 

First create a measure to count the adults - same as what you have provided.

Then, create a measure Earned Income Status (Maintained or Increased) using above measure - 

Earned Income Status (Maintained or Increased) = 
VAR _count = 
CALCULATE(
    [Count Adults], 
    'PDE_EE-EnrollmentsTbl'[Earned Income.Change Status] IN {"Increased", "Maintained"}
)
RETURN
DIVIDE(_count, [Count Adults], 0)

This measure will use the first measure where you have already calculated total count of adults. Using variable _count you are capturing total adults with status increased and maintained and then dividing _count with total count of adults measure.

Result - 

💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster

Best regards,
Rupasree Achari | BI & Fabric Analytics Engineer 

[cmorales1068]

@muom  

Oh yeah, this happened to me many times its usually because of a  fan-out. Your card measure counts each Exit once (because of the OR), but your table breaks it out by category, so if an exit has more than one Enrollment record (like it got reassessed and has both an "Increased" row and a "Maintained" row), it gets counted once in EACH row of the table — inflating the sum.

A quick way to check is group your Enrollments table by Entry Exit ID and see if any IDs show up with more than one distinct Change Status. If so, that's your culprit.

And if thats it to fix it pick one Change Status per exit (latest record, usually) before it hits the table,  like a calculated column or a summarized table instead of letting the visual count every related Enrollment row

[Ashish_Mathur]

Hi,

Please share the download link of the PBI file.

[muom]

I can't share the file directly due to security. Is there specific info I can provide to you that would help answer my question?

[Ashish_Mathur]

If possible, please anonymise the information and then share the download link.  Thank you.