Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
Hoping someone could help me on how to divide a Total divided by the amount of days a shift was worked.
In my table I have "# of Patients Per Shift" that displays the # of patients per shift per day with a total at the bottom of the table. I'm trying to figure out a formula that will divide the total # of Patients Per Shift divided by the # of days a Provider was working on those days.
Solved! Go to Solution.
Hi @jonnyA ,
I have built a data sample:
According to your description, it seems that you want to change the total average rather than the each cell as shown below:
If so,please try:
Average=
VAR _totalAvg =
DIVIDE (
SUMX ( 'vw_Test Dataset_Discharge_Dataset', [# of Patients Per Shift] ),
DISTINCTCOUNT ( 'vw_Test Dataset_Discharge_Dataset'[Shift Name] )
)
RETURN
IF (
ISINSCOPE ( 'vw_Test Dataset_Discharge_Dataset'[Shift Name] ),
[Your original Measure],
_totalAvg
)
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much for your formula, high five!!!
I had to change Shift Name to DOS, so when I did that your formula worked!!!
Thank you so much for your time and effort and for providing the data sample, that helps a lot!!!
Trying to mark yours as solution, but do not have the option?
Thank you so much for your formula, high five!!!
I had to change Shift Name to DOS, so when I did that your formula worked!!!
Thank you so much for your time and effort and for providing the data sample, that helps a lot!!!
Trying to mark yours as solution, but do not have the option?
Hi @jonnyA ,
I have built a data sample:
According to your description, it seems that you want to change the total average rather than the each cell as shown below:
If so,please try:
Average=
VAR _totalAvg =
DIVIDE (
SUMX ( 'vw_Test Dataset_Discharge_Dataset', [# of Patients Per Shift] ),
DISTINCTCOUNT ( 'vw_Test Dataset_Discharge_Dataset'[Shift Name] )
)
RETURN
IF (
ISINSCOPE ( 'vw_Test Dataset_Discharge_Dataset'[Shift Name] ),
[Your original Measure],
_totalAvg
)
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |