Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
Babycakes
Helper II
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.

 

Babycakes_0-1661478347720.png

 

 

Tthanks

10 REPLIES 10
Babycakes
Helper II
Helper II

Need ah = AH = [Actual contract hours)

Babycakes
Helper II
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 =

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

 

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

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
 

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/

I have already summed

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/

Need ah = AH = [Actual contract hours)

 

actual contract value is not to be summed just exact value

Hence 68-50 =18

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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