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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Wise1
Helper I
Helper I

DAX Formula for subtracting

Hello All,

 

I have been trying to figure the following out for the last 2 days without any luck, 

 

Basiclly what iam trying to achieve is 

Client - has allocated 10 hours a month

4 hrs worth of work is generated

 

10-4hrs = 6 hrs remain

The following is what iam working with; 

 

* Direct Query SQL 

Tables.jpg

 

 

 

 

1. Create a dax formula to combine hours spent on a "client" 

SLAHrsCalculatedPerClient = sla[MeasureTotalTimespentOrg]

 

2. Attempted to make another measure to minus the caculated time (but it doesnt show up) 

SLAHrsMinusFromAllocated = sla[MeasureTotalTimespentOrg]-  "SLA HOURS"

 

FormulaToMinus.jpg

 

 

 

however if i manually type the following it minus's the correct amount

SLAHrsCalculatedPerClient = sla[MeasureTotalTimespentOrg]-5 

 

Am i overlooking something with the above, or just not going about it the right way entirely? 

 

i have tried looking at a few other ways of doing this without luck, I hope enough information is provided

 

31 REPLIES 31

Actually looking at this closer I think you need to use a SUM in your measures

 

 

SLAHrsTotal = SUM(sla[SLA Hours])

 

SLAHrsCalculatedPerClient = SUM(sla[MeasureTotalTimespentOrg])

 

 

SLAHrsMinusFromAllocated = [SLAHrsTotal] - [SLAHrsCalculatedPerClient]

 

 

Thanks for your reply

Problem is the "SLA Hours" data is pulled directly from SQL as is (it comes up with error when trying to SUM) (i assume its because there is nothing to sum as its data entered) 

 

 

i have tried to do a "vlookup" of the data but its not giving me the option to type when i try enter the formula, or just something to print the data out to enable me to minus hours entered against it 

 

IE: 

SLAHours.jpg

DAXQuery.jpg

 

 

Can you convert the SLA Hours to values on import in the Query editor?

I cant do anything under the Query Editor as its using SQL Direct access (i think its called) 

- Still learning PowerBi so if there is a way to do it, im willing to give it a shot or do some reading on how its done 

Are you editing things in Power BI Desktop?

 

https://www.simple-talk.com/sql/bi/direct-query-power-bi/

 

 

Yep, using powerBi Desktop and using Direct Query

What is the data type for the field in SQL? What I can see from your screen shot, it is treating the field as string than number field. Can you click on the table as per screen shot 1 and then select modelling table, select field and change it to number (not sure if direct query allows that), if not then probably you need to create a column using dax and convert it to number:

 

table1.PNG

 

 

table2.PNG

 


DAX Convert String to Value

 

Add new column called, for example new column is called myValue

 

myValue = VALUE(Table1[TextField])

I didn't tested above formula but we can always fix it in case there is an issue.

 

Thanks,

 

 

 

 

 

 

 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks,

 

The PowerBi desktop doesnt have that circled calandar icon, not sure if its the same thing, but i have changed the type here; 

 

 

DAXQuery1.jpgDAXQuery2.jpg

 

Yes this is correct, becuase of live connection, you don't see that circled image which I sent. My bad.

 

Hope it fixed your issue.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks for taking the time to reply, 

It didnt fix the issue, as its not giving me the option to minus it, unless i can convert it to a measure somehow?

 

 

DAXQuery3.jpg

 

 

Just changing it to Data Type Decimal Number may work,  you don't then need the VALUE formula

 

 

Text to Number.PNG

I see now where you mean data type was set as text 

ive changed it to decimal number now 

 

 

myValue = sla[SLA Hours] - Allocated SLA hours

MeasureTotalTimespentOrg = SUM(view_tm_audit[TimeSpent]) - Actual time spent which is summed up to be minus'd from allocated hrs

 

myValue1 = sla[SLA Hours]-sla[MeasureTotalTimespentOrg]

 

Ignore that i dont have the myValue1 in the below pic, was just one i made up for a screenshot 

 

DAXQuery4.jpg

 

 

 

 

 

Build this first:

 

SLAHours = Sum(sla[SLA Hours])

 

 

Then this..

 

 

SLAHrsCalculatedPerClient = SUM(sla[MeasureTotalTimespentOrg])

 

 

Finally this

 

SLAHrsMinusFromAllocated = [SLAHrsTotal] - [SLAHrsCalculatedPerClient]

 

 

 

Hello,

 

Just wondering if there was a way to list the value (Rather then sum it up) 

 

 

Ie:   SLAHours = Sum(sla[SLA Hours]) (this adds up the allocated hours together) 

 

is it possible to use something to list the data, like ALL, Datatable, etc? 

 

Thanks!

Can you explain what you are trying to achieve by listing the values

 

If you want a table to show all the unique values then you could drag sla(SLA Hours) into the Rows section of a Matrix visualisation?

 

Thanks for spending the time you have with me so far, I hope this explains it ok for you, dont worry about the "time spent" i pulled that from another area which is off the screen, ive just added the same DAX and dragged it up for the below screenshot, 

 

 

 

 

DAXExplained.jpg

So where is your TimeSpent measure coming from?

 

If you put your SLAHhrsSUM and TImeSpent into the values fields of the matrix does that not work?   Then add an additional measure that subtracts one from the other?

 

Also how is Computer showing twice, is there something else differentiating the rows?

 

SLA.PNG

The Time Spent Measure comes from another place; 

"view_tm_audit"

 

TimeSpent = [time_spent]/view_tm_audit[NumeratorSwitch]

NumeratorSwitch = SWITCH(view_tm_audit[time_spent_units_description],"Days",24,"Hours",1,"Minutes",60,"Second",60*60)

- This calc's correctly 

 

The "Computer twice" is just two test jobs/cases ive rasied (screenshot has another one in it because 

you could refer to those as "ClientA-Job1" and "ClientA-Job2" 

 

"ClientA-Job1" - Labour Spent: 0.25

"ClientA-Job2" - Labour Spent: 2.50

 

Its just to make sure it can add time up correctly 

 

 

When i add the "SLAMinus" in with it i get the following; 

SLAHrsSUM = SUM(sla[SLA Hours])

SLAHrsMinusFromAllocated = (sla[SLAHrsCalculatedPerClient] - sla[SLAHrsSUM])

 

 

The one strange thing is i cant get 'sla[SLA Hours]' to appear with out converting it to a DAX calc first 

 

if i could just minus from it directly, i think it would be fine!  (Rather then suming it up) 

ie:   SLA_Minus = (sla[SLAHrsCalculatedPerClient] - sla[SLA Hours]) 

 

 

Sorry @Wise1,  I'm not understanding the concept of what SLAMinus is and why you think you need to minus is directly rather than use another measure :  [SLAHours]-[TimeSpent]

Sorry,

 

The idea behind this concept is for me to get an overview of clients with managed services. (at the moment is a manual process) 

 

Customers prepay lets say 10 support hours, with every case raised, labour time is deducted from the 10 support hours

 

Problem is when i try and manually minus it, it does not show up unless its a caculated field (i think that is best way to describe it) 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.