Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
villa1980
Resolver I
Resolver I

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 🙂

villa1980_0-1730906115282.png

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

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
rajendraongole1
Super User
Super User

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 . 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

2 REPLIES 2
v-kaiyue-msft
Community Support
Community Support

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
Super User
Super User

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 . 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.