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]
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!
- wynhopkins9 years agoMost Valuable Professional
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?
- Wise19 years agoHelper 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,
- wynhopkins9 years agoMost Valuable Professional
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?
- Wise19 years agoHelper 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;
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])
- wynhopkins9 years agoMost Valuable Professional
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]
- Wise19 years agoHelper 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)
- wynhopkins9 years agoMost Valuable Professional
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
- Wise19 years agoHelper I
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!