Forum Discussion
DAX Formula for subtracting
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
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
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!
