Forum Discussion

ahiemstra's avatar
ahiemstra
Helper I
1 year ago
Solved

Measure incorporating slicer value

I'm attaching a PBIX file and hoping someone might be able to assist. I've created two separate measures: Avg Encounters/Day Discipline and Avg Encounters/Day Provider. The calculation for both me...
Need Help
  • ahiemstra's avatar
    ahiemstra
    1 year ago

    I spent some more time working on the DAX and was able to adjust the part in the denominator that wasn't calculating correctly, and now it's working as expected.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi ahiemstra hello Greg_Deckler, thank you for your prompt reply!

 Try the following measures to meet your requirements:

Avg Encounters/Day Discipline2 = 
VAR TotalEncounters = CALCULATE(
    COUNT('1 ON 1'[Encounter Time]),
    '1 ON 1'[Activity Description] = SELECTEDVALUE('1 ON 1'[Activity Description]),REMOVEFILTERS('1 ON 1'[Employee Full Name]))

VAR TotalDays = CALCULATE(
    DISTINCTCOUNT('1 ON 1'[Encounter Date]),
    '1 ON 1'[Activity Description] = SELECTEDVALUE('1 ON 1'[Activity Description]),
    REMOVEFILTERS('1 ON 1'[Employee Full Name])  
)

RETURN

DIVIDE(TotalEncounters, TotalDays, 0)

Avg Encounters/Day Provider2 = 
VAR TotalEncounters = CALCULATE(
    COUNT('1 ON 1'[Encounter Time]),
    '1 ON 1'[Employee Full Name] = SELECTEDVALUE('1 ON 1'[Employee Full Name]),
    REMOVEFILTERS('1 ON 1'[Activity Description])  
)

VAR TotalDays = CALCULATE(
    DISTINCTCOUNT('1 ON 1'[Encounter Date]),
    '1 ON 1'[Employee Full Name] = SELECTEDVALUE('1 ON 1'[Employee Full Name]),
    REMOVEFILTERS('1 ON 1'[Activity Description])  
)

RETURN

DIVIDE(TotalEncounters, TotalDays, 0)

 Result for your reference:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

ahiemstra's avatar
ahiemstra
Helper I
1 year ago

Hi Anonymous,

 

Thanks for your response. I think we're making progress.

 

It seems there may be an issue with the TotalDays component. While it's calculating correctly for the Avg Encounters/Day Provider2 measure, it's giving incorrect results for the Avg Encounters/Day Discipline2 measure.

 

The values for the Avg Encounters/Day Discipline2 measure are currently too high. For instance, in April, the discipline average should be around 4.0, but in your chart, it’s showing just under 20.

 

The result shuold look more like this:

 

I've successfully used standalone versions of the discipline and provider measure in other PBIX files, and the DAX that works properly in those cases looks like this:

 

Avg Encounters/Day = COUNT('1 ON 1'[Encounter Time]) /
COUNTX(
SUMMARIZE('1 ON 1', '1 ON 1'[Employee ID], '1 ON 1'[Encounter Date]),
'1 ON 1'[Encounter Date]
)

 

Would it be possible to update your measures to use this DAX formula while also incorporating the SELECTEDVALUE and REMOVEFILTERS components you've used?