How to create a DAx formula to separate out 2 Metrics

[Karolina411]

Hello Dax Wizards. So I have 2 metrics to compare infections to  and wrote this formula to achive this:

# NHSN SS Infections Numerator = SUM(NhsnInfData[hfNumer])

# NHSN Surgical Procedure Denominator = SUM(NhsnInfData[hfDenom])

# Surgical Procedures = COUNT(NhsnInfData[MRN])

QSRC Avg Colo = AVERAGE(NhsnInfData[HspGoalSsiColo])

--Formula to get either Hospital or System goal

QSRC SSI Colo Goal = CALCULATE(

--VAR Hsp =  

VAR HspGoal = AVERAGE(NhsnInfData[HspGoalSsiColo])    --[QSRC Avg Colo]

VAR SysGoal = AVERAGE(NhsnInfData[SysGoalSsiColo])

VAR HierarchyGoal = IF(

                        ISINSCOPE(NhsnInfData[AssocLocHsp]) ,

                        HspGoal,

                        SysGoal

)

RETURN HierarchyGoal

)

Here is what the data is and it is not calculating correctly.  Does anyone have a similar example where you must compare an average to 2 different metrics? 

[Anonymous · Accepted solution]

Hi @Karolina411 ,

May I ask what your expected results are? Your expected results seem to be related to the ISINSCOPE function, hopefully these links will be helpful: ISINSCOPE: The Key to Dynamic Data Drilldowns - Ethan Guyant

Use IsInScope to get the right hierarchy level in DAX - Kasper On BI

Can you share sample data and sample output in table format?  Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Clara Gong

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