Total does not match individual rows For Bay Description

[villa1980]

Me Again,

I have 2 tables joined by Date (Calendar and Appointment).

Appointment does not have full days due to nature of bookings.

Calendar has full days for each month and has a open time of 42,900 for the month.

Appointment table contains, Depot, Bay_Desc, Blocked_Mins and Booked_Mins
I have created a seperate metric table that returns CM Tot Mins, CM Available Mins and % available.

However, when I look at the totals at the bottom of the table the total for CM_Tot_Mins, CM_Available_Mns and CM%_Av_Mins do not match the individual rows... I have pasted the table below and my DAX for the metrics.
Guys I need your wisdom 🙂

DAX

Block_Bay_mins = CALCULATE(

    sum('EXTR_STG_FOCUS APPOINTMENTS'[Blocked_Bay_Mins]),

    'EXTR_STG_FOCUS APPOINTMENTS'[Appt_Type] = "Blocked"

)

Booked_Bay_Mins = CALCULATE(

    sum('EXTR_STG_FOCUS APPOINTMENTS'[Booked_Appt_mins]),

    'EXTR_STG_FOCUS APPOINTMENTS'[Appt_Type] = "Booked")

CM_Tot_Mins = SUMX(

    VALUES('EXTR_STG_FOCUS APPOINTMENTS'[Centre Name]),

    SUM('Calendar'[CM_Tot_Mins])

)

CM_Available_Mins = [CM_Tot_Mins] - (Sum('EXTR_STG_FOCUS APPOINTMENTS'[Blocked_Bay_Mins]) + sum('EXTR_STG_FOCUS APPOINTMENTS'[Booked_Appt_mins]) )

CM_%_Av_Mins = DIVIDE([CM_Available_Mins],[CM_Tot_Mins])

[v-kaiyue-msft]

Hi @villa1980 ,

This is a question that gets asked a lot. Technically, the total row is correct for the measure, just not what most people expect. What one would expect is for “Total” to show the sum of the values in the column. The measure does not perform this operation. The measure follows the context of the Total row and is calculated within that context. Therefore, a Measure used in a column of a table visualization effect may have unexpected values in the Total column.

You can refer to these solutions:
Measure Totals, The Final Word - Microsoft Fabric Community
Why Power BI totals might seem inaccurate - SQLBI

If this doesn't solve your problem. 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 there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[rajendraongole1 · Accepted solution]

Hi @villa1980 - you can adjust each measure to ensure consistent results across individual rows and the total row

Block_Bay_mins =
CALCULATE(
SUM('EXTR_STG_FOCUS APPOINTMENTS'[Blocked_Bay_Mins]),
'EXTR_STG_FOCUS APPOINTMENTS'[Appt_Type] = "Blocked"
)

Booked_Bay_Mins =
CALCULATE(
SUM('EXTR_STG_FOCUS APPOINTMENTS'[Booked_Appt_mins]),
'EXTR_STG_FOCUS APPOINTMENTS'[Appt_Type] = "Booked"
)

CM_Tot_Mins =
SUMX(
VALUES('EXTR_STG_FOCUS APPOINTMENTS'[Centre Name]),
CALCULATE(SUM('Calendar'[CM_Tot_Mins]))
)

you can update cm_available mins to calcualte properly.

CM_Available_Mins =
[CM_Tot_Mins] - ([Block_Bay_mins] + [Booked_Bay_Mins])

Adjust CM_%_Av_Mins to Respect Row and Total Context

CM_%_Av_Mins =
DIVIDE([CM_Available_Mins], [CM_Tot_Mins])

Hope the above calculation helps to solve .