cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper II

Sum value less actual value in POwer BI

HI There

I have a sum value eg Staff member has 10 shift totalling 68 hours and set value of 50 hours minimum

How do i create a measure to get the diffeence.  Rostered hours is a sum of x staff member, Actual contract value is set value.

Tthanks

10 REPLIES 10
Helper II

Need ah = AH = [Actual contract hours)

Helper II

Actual contract hours is set value,

Total shift hours is sum of all hours per staff member

Need Shift hours - Actual contract hours =

Super User

Hi @Babycakes ,

Create a measure that will aggregate/sum the two columns and then get the difference

``````DIFFERENCE measure =
SUM('Table'[Column1]) -  SUM('Table'[Column2])``````

or you can create a calculated column to get the difference

``````DIFFERENCE Column =
'Table'[Column1] -  'Table'[Column2]``````

and then create another measure  the sum the difference

``````SUM OF DIFFERENCE =
SUM ( 'Table'[DIFFERENCE Column] )``````

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

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Helper II

 Staff Start Finish Total shift hours Day Actual contract hours Total rostered hours SIMPSON John 2:00 PM 4:00 PM 2 Monday 40 49.66666667 SIMPSON John 9:00 AM 1:30 PM 4.5 Wednesday 40 49.66666667 SIMPSON John 2:00 PM 4:00 PM 2 Thursday 40 49.66666667 SIMPSON John 2:00 PM 7:00 PM 5 Thursday 40 49.66666667 SIMPSON John 4:00 PM 7:00 PM 3 Friday 40 49.66666667 SIMPSON John 5:00 PM 9:00 AM Next day 8 Sunday 40 49.66666667 SIMPSON Homer 3:00 PM 7:00 AM Next day 8 Tuesday 50 68 SIMPSON Homer 7:00 AM 3:00 PM 8 Monday 50 68 SIMPSON Homer 2:00 PM 3:00 PM 1 Tuesday 50 68 SIMPSON Homer 9:00 AM 5:00 PM 8 Wednesday 50 68 SIMPSON Homer 3:00 PM 4:00 PM 1 Thursday 50 68 SIMPSON Homer 3:00 PM 4:00 PM 1 Friday 50 68 SIMPSON Homer 7:00 AM 3:00 PM 8 Thursday 50 68 SIMPSON Homer 7:00 AM 3:00 PM 8 Friday 50 68 SIMPSON Homer 7:00 AM 3:00 PM 8 Saturday 50 68 SIMPSON Homer 3:00 PM 7:00 AM Next day 8 Friday 50 68 SIMPSON Homer 2:00 PM 3:00 PM 1 Friday 50 68 SIMPSON Homer 7:00 AM 3:00 PM 8 Sunday 50 68 SIMPSON John 12:00 PM 5:00 PM 5 Tuesday 40 49.66666667 SIMPSON John 12:00 PM 5:00 PM 5 Tuesday 40 49.66666667 SIMPSON John 2:50 PM 6:00 PM 3.16666666666667 Monday 40 49.66666667 SIMPSON John 10:00 AM 12:00 PM 2 Monday 40 49.66666667 SIMPSON John 10:00 AM 12:00 PM 2 Monday 40 49.66666667 SIMPSON John 10:00 AM 12:00 PM 2 Tuesday 40 49.66666667 SIMPSON Lisa 2:45 PM 8:15 PM 5.5 Tuesday 60 66.5 SIMPSON John 9:00 AM 3:00 PM 6 Friday 40 49.66666667 SIMPSON Lisa 3:00 PM 11:00 PM 8 Monday 60 66.5 SIMPSON Lisa 8:00 AM 3:00 PM 7 Monday 60 66.5 SIMPSON Lisa 7:00 AM 11:00 AM 4 Wednesday 60 66.5 SIMPSON Lisa 1:00 PM 3:00 PM 2 Wednesday 60 66.5 SIMPSON Lisa 3:00 PM 5:00 PM 2 Wednesday 60 66.5 SIMPSON Lisa 9:00 AM 1:00 PM 4 Wednesday 60 66.5 SIMPSON Lisa 8:00 AM 3:00 PM 7 Tuesday 60 66.5 SIMPSON Lisa 7:00 AM 11:00 AM 4 Thursday 60 66.5 SIMPSON Lisa 11:00 AM 1:00 PM 2 Thursday 60 66.5 SIMPSON Lisa 11:00 AM 5:00 PM 6 Wednesday 60 66.5 SIMPSON Lisa 8:00 AM 3:00 PM 7 Saturday 60 66.5 SIMPSON Lisa 3:00 PM 11:00 PM 8 Sunday 60 66.5
Helper II

However there are filters by staff Name (not shown in screen shot for privacy)

C = Calculate(Sum('PW Staff Templated Hours Report'[Total rostered hours])-CALCULATE(sum('PW Staff Templated Hours Report'[Actual contract hours])))

However I dont want sum of "sum('PW Staff Templated Hours Report'[Actual contract hours])))  "  Need just actual value

Super User

Hi,

Write these measures

RH = Sum('PW Staff Templated Hours Report'[Total rostered hours])

AH = sum('PW Staff Templated Hours Report'[Actual contract hours])

Diff = [AH]-[RH]

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

I have already summed

Super User

Well then i cannot understand your question.  Share the download link of your PBI file and show the result clearly.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

Need ah = AH = [Actual contract hours)

Helper II

actual contract value is not to be summed just exact value

Hence 68-50 =18

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors