Forum Discussion

Wise1's avatar
Wise1
Helper I
9 years ago

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 ...
Need Help
Wise1's avatar
Wise1
Helper I
9 years ago

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's avatar
wynhopkins
Most Valuable Professional
9 years ago

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's avatar
    Wise1
    Helper I
    9 years ago

    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) 

     

  • wynhopkins's avatar
    wynhopkins
    Most Valuable Professional
    9 years ago

    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

  • Wise1's avatar
    Wise1
    Helper I
    9 years ago

    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!