Totaling Multiple Filtered Measures

[Anonymous]

Hello,

I am putting together a type of weighted caseload for my staff members. It is kind of complex and i am wondering if anyone has any ideas or better ways to go about this or even a dax command that may work. Essentially each staff member sees a certain amount of poeple daily. Each person recieves different services and so on that derive from different SQL tables that i have. I have created several individual table visuals getting the correct total data for each category (Total of each visual is filtered in the visual) with measures. However is there a way to add the filtered Total data in a measure for each category up as a whole to find their total score for all the people they see if that makes sense? This is example data below by the way as i cannot list the actual data.

Here is an example. I have a visual filtered to look back one year, then i have applied this measure to it to count each row of data and multiply it by 20 to get the score for each row.

Inpatient 1 Year Score = COUNT(Admissions[Case#])*20

Next i have another visual total adding all of their 5 year stays together similar to the one above.  

Inpatient 5 Year Score = COUNT(Admissions[Case#])*5

Hence for every patient in the last year with a admission would recieve 20 points for each admission and every patient admission in the last 5 years would receive 5 points per admission

example

Would it be easier to create a scoring table and add it in? This may be something not doable in Powerbi as well i am not sure. Her total score then for admissions would be 30 and i would add that to a bunch of total visuals like this one. Maybe i need to make these column dax statements too instead of measures? Any help would be greatly appreciated!

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Please check the measure below.

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[case] ),
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[date] >= EDATE ( TODAY (), -12 ) )
) * 20
    + CALCULATE (
        DISTINCTCOUNT ( 'Table'[case] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[date] >= EDATE ( TODAY (), -60 ) )
    ) * 5

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

Is there anyway to get this to add up correctly when you take the individual filter off too? I am finding that it adds quite a bit to the score number when taking the individual filter off by person and looking at everyone as a whole.

[Anonymous]

I have one more question  as well using the below dax statement. I want to add another total to this but i want to filter by a specific value name in a column instead of date. What would that entail?

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[case] ),
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[date] >= EDATE ( TODAY (), -12 ) )
) * 20
    + CALCULATE (
        DISTINCTCOUNT ( 'Table'[case] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[date] >= EDATE ( TODAY (), -60 ) )
    ) * 5

[Anonymous]

Thank you so much!