Measure divided by # days worked (10.13.21)

[jonnyA]

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.

# of Patients Per Shift = CALCULATE(COUNT(vw_Test Dataset_Discharge_Dataset[Procedure_Code]))

[v-eqin-msft · Accepted 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.

[jonnyA · Accepted solution]

 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?

[jonnyA · Accepted solution]

 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?

[jonnyA · Accepted solution]

@v-eqin-msft 

[v-eqin-msft · Accepted 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.

[lbendlin]

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.