DAX Formula for subtracting

[Wise1]

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

[wynhopkins]

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]

[Wise1]

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: 

[wynhopkins]

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

[Wise1]

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 

[wynhopkins]

Are you editing things in Power BI Desktop?

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

[Wise1]

Yep, using powerBi Desktop and using Direct Query

[parry2k]

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:

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,

[Wise1]

Thanks,

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

[parry2k]

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.

[Wise1]

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?

[wynhopkins]

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

[Wise1]

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 

[wynhopkins]

Build this first:

SLAHours = Sum(sla[SLA Hours])

Then this..

SLAHrsCalculatedPerClient = SUM(sla[MeasureTotalTimespentOrg])

Finally this

SLAHrsMinusFromAllocated = [SLAHrsTotal] - [SLAHrsCalculatedPerClient]

[Wise1]

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!

[wynhopkins]

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?

[Wise1]

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, 

[wynhopkins]

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?

[Wise1]

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

[wynhopkins]

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]

[Wise1]

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)