Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Im using countdistinct for "Patients seen", however, the total is not summing correctly. See pic. Can any one help?
Solved! Go to 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):
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.
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.
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!!!
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):
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?
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] )
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |