Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Babycakes
Helper II
Helper II

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
677 Views
0
10 REPLIES 10
Babycakes
Helper II
Helper II

‎08-25-2022 08:34 PM

Need ah = AH = [Actual contract hours)

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

‎08-25-2022 08:32 PM

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
609 Views
0
danextian
Super User
Super User

‎08-25-2022 07:28 PM

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

‎08-25-2022 11:04 PM

 

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
590 Views
0
Babycakes
Helper II
Helper II
In response to danextian

‎08-25-2022 07:35 PM

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
636 Views
0
Ashish_Mathur
Super User
Super User
In response to Babycakes

‎08-25-2022 07:41 PM

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
633 Views
0
Babycakes
Helper II
Helper II
In response to Ashish_Mathur

‎08-25-2022 08:03 PM

I have already summed

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

‎08-25-2022 08:08 PM

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
627 Views
Babycakes
Helper II
Helper II
In response to Ashish_Mathur

‎08-25-2022 08:43 PM

Need ah = AH = [Actual contract hours)

 

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

‎08-25-2022 08:09 PM

actual contract value is not to be summed just exact value

Hence 68-50 =18

 

Message 7 of 11
625 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All