SUMX and Subtotals Not Adding Up

[TheMacroNoob]

Hello experts,

In my Power BI report I am trying to do a calculation on all rows in a table, then be able to correctly add them up in the subtotal lines by whatever grouping I am using. In my case, the final grouping I have in Matrix rows is 'Calendar'[EOM], which is not being used in the measure, and that is the only subtotal line that isn't mathing. Despite correct relationships being set up, is this what is causing the issue, and do I need to adjust my measure to be able to be grouped outside of ClosedClinic fields/Calendar[Date]?

I have the following measure in Power BI:

Estimated Lost Visits =

SUMX(
    ClosedClinics,
    VAR AvgVisitsR90 = 
        CALCULATE(
            AVERAGE(VisitType[Total Visits]),
            VisitType[Total Visits] > 0,
            DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-90,DAY),
            WEEKDAY('Calendar'[Date],2) = WEEKDAY(MAX('Calendar'[Date]),2)
        )
    VAR AvgHrsWeekday =
            CALCULATE(
            AVERAGE(VisitType[Hours]),
            VisitType[Total Visits] > 0,
            DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-90,DAY),
            WEEKDAY('Calendar'[Date],2) = WEEKDAY(MAX('Calendar'[Date]),2)
        )
    VAR VisitsPerHour = DIVIDE(AvgVisitsR90, AvgHrsWeekday, 2.8)
    RETURN 
    VisitsPerHour * ClosedClinics[True Hours Lost]
)

In a Matrix, I am using the following fields, all but one being used in the measure:

 

[Matt Allington]

There’s a section in my article that covers “totals don’t add up”, but I suggest you read the entire article

When to Use SUMX() vs SUM() in DAX

Learn the main insights of SUMX() vs SUM() in DAX with examples, so you can decide which formula works best to get excellent reports.?

exceleratorbi.com.au