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
jonnyA
Responsive Resident
Responsive Resident

My Countdistinct Total is not summing correctly

Im using countdistinct for "Patients seen", however, the total is not summing correctly.  See pic.  Can any one help?

jonnyA_0-1620328169995.png

 

1 ACCEPTED SOLUTION

OK, you should avoid implicit measures, this is a measure where you drag a field from a table into a view then pick "Summarize By": Count.  Instead you want to write a measure that does what you want, that way you can use it other places.

So, New Measure.

 

Patient Count = DISTINCTCOUNT ( vw_???_DocAudit[PatientName] )

 

Then we make the next one that sums correctly.

 

Patient Count Totaling = SUMX ( VALUES ( vw_???_DocAudit[ProcedureCode] ), [Patient Count] )

 

The second measure uses the first one we made.

You should end up with something like this (we call them claimants rather than patients): 

jdbuchanan71_0-1620401947269.png

 

View solution in original post

13 REPLIES 13
daxer-almighty
Solution Sage
Solution Sage

It's summing up correctly. You can't just sum up the column to get the total. You have to count the distinct id's within the VISIBLE period of time (I think you are breaking up Patients Seen by time here, right?) to arrive at the correct figure.

Yes, I see what you are saying.  I still prefer them to sum correctly though.  I could jus take it off.

jonnyA
Responsive Resident
Responsive Resident

@jdbuchanan71 , Like this ?  Power Bi not liking my measure (see the red squiggly at the end?)

jonnyA_0-1620329369210.png

 

You need a measure for [Patients Seen] which will be something like 

 

Patients Seen = DISTINCTCOUNT ( vw_???_DocAudit[PatientID] )

 

You are better off counting a unique ID rather than a name incase some patients have the same name.  Then you use that measure in your sum version.

 

Patients Seen Sum = SUMX ( VALUES ( vw_???_DocAudit[PatientName] ), [Patients Seen] )

 

@jdbuchanan71 I tried your suggestions, however, the total sum is still incorrect.

@jonnyA  

That is not enough information for me to help you.  You need to show me the measures you have used and the category of data you are trying to slice it up by.

Ok, so I am using the following ...

Procedure Code ... 99281 through 99292

PatientName ... Count(Distinct)

PatientName ... Count(Distinct) shown as Percent of Grand Total

For now, ignore the calculated measures I created for this dataset.

I have 2 issues outstanding that I cannot figure out ...

1. I'd prefer the "Count of PatientName" column to sum correctly.  Should be 18,745, not 15,465.

2. The "%GT Count of PatientName" is not summing correctly either.  If you add up those %'s you'll get 121.20%  I would think those %'s should add up to 100%?

Thank you for your help already and I appreciate help going forward too, greatly appreciated!!!

jonnyA_0-1620395927447.png

 

OK, you should avoid implicit measures, this is a measure where you drag a field from a table into a view then pick "Summarize By": Count.  Instead you want to write a measure that does what you want, that way you can use it other places.

So, New Measure.

 

Patient Count = DISTINCTCOUNT ( vw_???_DocAudit[PatientName] )

 

Then we make the next one that sums correctly.

 

Patient Count Totaling = SUMX ( VALUES ( vw_???_DocAudit[ProcedureCode] ), [Patient Count] )

 

The second measure uses the first one we made.

You should end up with something like this (we call them claimants rather than patients): 

jdbuchanan71_0-1620401947269.png

 

Wow, that was awesome!!!  Thank you so much!!!

 

And if anyone else wants to know, I had this question i postyed earlier ... 2. The "%GT Count of PatientName" is not summing correctly either.  If you add up those %'s you'll get 121.20%  I would think those %'s should add up to 100%?  Solution for that is to use the measure I just created ... Patient Count Totaling = SUMX ( VALUES ( vw_???_DocAudit[ProcedureCode] ), [Patient Count] ) ... and then, SHOW VALUE AS ... % of Grand total.

Thanks again for the help, so pumped!!!

@jonnyA, why do you think that the measure [Patients Seen] is additive? And which dimension do you think it is additive across?

jdbuchanan71
Super User
Super User

That just means that some of the patients in the 8516 are also in the 6001 for example and then when you look at in total those people only get counted once.

The total row has not concept of the numbers above it.  It calculates the DISTINCTCOUNT with no category filter applied (whatever you have in the first column that is splitting up the number).

@jdbuchanan71 , Ok, i see what you are saying, however, shouldn't there be a way for that total to sum correctly?

There is, you would use something like this.

Patients Seen Sum = SUMX ( values ( whatever your category column is here ), [Patients Seen] )

 

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.