cancel
Showing results for
Did you mean:

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

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

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"

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
MVP

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]

Helper I

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:

MVP

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

Helper I

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

MVP

Are you editing things in Power BI Desktop?

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

Helper I

Yep, using powerBi Desktop and using Direct Query

Super User

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:

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.

Helper I

Thanks,

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

Super User

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

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.

Helper I

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?

MVP

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

Helper I

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

MVP

Build this first:

SLAHours = Sum(sla[SLA Hours])

Then this..

SLAHrsCalculatedPerClient = SUM(sla[MeasureTotalTimespentOrg])

Finally this

SLAHrsMinusFromAllocated = [SLAHrsTotal] - [SLAHrsCalculatedPerClient]

Helper I

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!

MVP

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?

Helper I

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,

MVP

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?

Helper I

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;

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

MVP

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]

Helper I

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)

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors