DAX Formula for subtracting

Hi Wise1,

You can use the SUM function in DQ mode if you go to Options -> DirectQuery and enable "Allow unrestricted measures in DirectQuery mode".:smileyhappy:

Regards

Build this first:

SLAHours = Sum(sla[SLA Hours])

Then this..

SLAHrsCalculatedPerClient = SUM(sla[MeasureTotalTimespentOrg])

Finally this

SLAHrsMinusFromAllocated = [SLAHrsTotal] - [SLAHrsCalculatedPerClient]

SLAHours = Sum(sla[SLA Hours]) 

- Problem is i cant add this up because the "SLA HOURS" is a set number which is set and differs (ie: some might be 10 hours, some 20) 

ie: Client A has - 10 hrs

     Client B has - 20 Hrs

When i sum it up- it just adds the "SLA Hours" up and gives me a number of 30 

But then when you put that in a table against client it should show you the total SLA hours by client

Hi Wise1,

In addition, use the formula below to create a measure, and show it with the Client id on the report should also work.:smileyhappy:

SLAHrsCalculatedPerClient = SUMX ( sla, sla[MeasureTotalTimespentOrg] - sla[SLA Hours] )

Regards

Hello,

Apologies for delay in reply - was driving back from interstate location 

- The formula works (and minus's the hours) but it first Adds the specified hours allocated to the client 

so ive setup 2 as a test 

ClientA - 20 hrs

ClientB - 10 Hrs

When i setup the dax to minus the hours it works (as you can see in picture) 

Is there a possibility for a "straight conversion" so to speak to pick up the value in the cell and just convert it into a calculated form? (vlookup doesnt seem to want to work) 

Thanks for replying, it does not seem to work with the method above, but works when i do it with only lets say [SLA Hours]

Just thought i would reply so people dont think i have forgotten about this thread

Still trying to pull it together, but getting closer! thanks everyone for their input so far! 

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, 

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?

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) 

So for presenting the data would you have customers on the rows then 3 measures in the values

[SLAHours],  [TimeSpent],  [TimeRemain]

Where

[SLAHours] = SUM(sla[**Name of Prepaid Hours Column**])

[TimeSpentBase] =  Sum(**TableName**[**Name of Time Spent Column**])

[TimeSpent] = DIVIDE( [TimeSpentBase], [NumeratorSwitch])

[TimeRemain] = [SLAHours] - [TimeSpent]

**Indicates you need to chose the correct Table / Column name

Hello,

Thanks for working with me throguh this, but it doesnt look like i can do what i need it to do through graphs

https://community.powerbi.com/t5/Desktop/Make-a-column-not-sum-count/td-p/24672

this is the issue im faced with and from reading it does not seem like there is a fix or solution yet, i have tried to change to decimal number, text, whole number

im going to try and look for a custom graph and see if i can do it with that!