Reply
Babycakes
Helper II
Helper II
Partially syndicated - Outbound

Sum value less actual value in POwer BI

‎08-25-2022 06:47 PM

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.

 

Babycakes_0-1661478347720.png

 

 

Tthanks

Labels:
Message 1 of 11
721 Views
0
10 REPLIES 10
Babycakes
Helper II
Helper II

‎08-25-2022 08:34 PM
Syndicated - Outbound

Need ah = AH = [Actual contract hours)

Message 11 of 11
652 Views
0
Babycakes
Helper II
Helper II

‎08-25-2022 08:32 PM
Syndicated - Outbound

Actual contract hours is set value,

Total shift hours is sum of all hours per staff member

 

Need Shift hours - Actual contract hours =

Message 10 of 11
653 Views
0
danextian
Super User
Super User

‎08-25-2022 07:28 PM
Syndicated - Outbound

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




Dane Belarmino | Microsoft MVP | Proud to be a Super User!

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


"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.
Message 2 of 11
683 Views
0
Babycakes
Helper II
Helper II
In response to danextian

‎08-25-2022 11:04 PM
Syndicated - Outbound

 

StaffStartFinishTotal shift hoursDayActual contract hoursTotal rostered hours
SIMPSON John2:00 PM4:00 PM2Monday4049.66666667
SIMPSON John9:00 AM1:30 PM4.5Wednesday4049.66666667
SIMPSON John2:00 PM4:00 PM2Thursday4049.66666667
SIMPSON John2:00 PM7:00 PM5Thursday4049.66666667
SIMPSON John4:00 PM7:00 PM3Friday4049.66666667
SIMPSON John5:00 PM9:00 AM Next day8Sunday4049.66666667
SIMPSON Homer3:00 PM7:00 AM Next day8Tuesday5068
SIMPSON Homer7:00 AM3:00 PM8Monday5068
SIMPSON Homer2:00 PM3:00 PM1Tuesday5068
SIMPSON Homer9:00 AM5:00 PM8Wednesday5068
SIMPSON Homer3:00 PM4:00 PM1Thursday5068
SIMPSON Homer3:00 PM4:00 PM1Friday5068
SIMPSON Homer7:00 AM3:00 PM8Thursday5068
SIMPSON Homer7:00 AM3:00 PM8Friday5068
SIMPSON Homer7:00 AM3:00 PM8Saturday5068
SIMPSON Homer3:00 PM7:00 AM Next day8Friday5068
SIMPSON Homer2:00 PM3:00 PM1Friday5068
SIMPSON Homer7:00 AM3:00 PM8Sunday5068
SIMPSON John12:00 PM5:00 PM5Tuesday4049.66666667
SIMPSON John12:00 PM5:00 PM5Tuesday4049.66666667
SIMPSON John2:50 PM6:00 PM3.16666666666667Monday4049.66666667
SIMPSON John10:00 AM12:00 PM2Monday4049.66666667
SIMPSON John10:00 AM12:00 PM2Monday4049.66666667
SIMPSON John10:00 AM12:00 PM2Tuesday4049.66666667
SIMPSON Lisa2:45 PM8:15 PM5.5Tuesday6066.5
SIMPSON John9:00 AM3:00 PM6Friday4049.66666667
SIMPSON Lisa3:00 PM11:00 PM8Monday6066.5
SIMPSON Lisa8:00 AM3:00 PM7Monday6066.5
SIMPSON Lisa7:00 AM11:00 AM4Wednesday6066.5
SIMPSON Lisa1:00 PM3:00 PM2Wednesday6066.5
SIMPSON Lisa3:00 PM5:00 PM2Wednesday6066.5
SIMPSON Lisa9:00 AM1:00 PM4Wednesday6066.5
SIMPSON Lisa8:00 AM3:00 PM7Tuesday6066.5
SIMPSON Lisa7:00 AM11:00 AM4Thursday6066.5
SIMPSON Lisa11:00 AM1:00 PM2Thursday6066.5
SIMPSON Lisa11:00 AM5:00 PM6Wednesday6066.5
SIMPSON Lisa8:00 AM3:00 PM7Saturday6066.5
SIMPSON Lisa3:00 PM11:00 PM8Sunday6066.5
Message 9 of 11
634 Views
0
Babycakes
Helper II
Helper II
In response to danextian

‎08-25-2022 07:35 PM
Syndicated - Outbound

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
 
Message 3 of 11
680 Views
0
Ashish_Mathur
Super User
Super User
In response to Babycakes

‎08-25-2022 07:41 PM
Syndicated - Outbound

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
https://www.linkedin.com/in/excelenthusiasts/
Message 4 of 11
677 Views
0
Babycakes
Helper II
Helper II
In response to Ashish_Mathur

‎08-25-2022 08:03 PM
Syndicated - Outbound

I have already summed

Message 5 of 11
674 Views
0
Ashish_Mathur
Super User
Super User
In response to Babycakes

‎08-25-2022 08:08 PM
Syndicated - Outbound

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
https://www.linkedin.com/in/excelenthusiasts/
Message 6 of 11
671 Views
Babycakes
Helper II
Helper II
In response to Ashish_Mathur

‎08-25-2022 08:43 PM
Syndicated - Outbound

Need ah = AH = [Actual contract hours)

 

Message 8 of 11
650 Views
0
Babycakes
Helper II
Helper II
In response to Ashish_Mathur

‎08-25-2022 08:09 PM
Syndicated - Outbound

actual contract value is not to be summed just exact value

Hence 68-50 =18

 

Message 7 of 11
669 Views
0
avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Stop
View All
Recommendations
Top Solution Authors (Last Month)
View All
Top Kudoed Authors (Last Month)
View All