Forum Discussion

villa1980's avatar
villa1980
Icon for Resolver II rankResolver II
1 year ago
Solved

Total does not match individual rows For Bay Description

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])

 

 

 

 

 

 

 

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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 . 

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    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 . 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.