Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Sum up total cost

hi team,

 

basically, i have two tables link to each other one has all the employee's name + their certificates 

 

the other has the details of the certificate which has the cost as well

 

i want to get the total cost for specific employees but i could now 

https://www.up-00.com/i/00165/llfpviwd73br.png

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Here is my test tables:

    Then create these columns:

    Course Time = DATEDIFF(Cost[Course Start Time],Cost[Course End Time],SECOND)/3600
    Cost = Cost[Course Time]*Cost[Course Cost]

    Please try this measure:

    Total Cost =
CALCULATE (
    AVERAGEX ( Cost, Cost[Course Cost] * Cost[Course Time] ),
    FILTER (
        ALLSELECTED ( Cost ),
        Cost[Certificates] IN FILTERS ( Cost[Certificates] )
    )
)

    The result shows:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

4 Replies

  • kentyler's avatar
    kentyler
    Icon for Solution Sage rankSolution Sage
    6 years ago

    I created some sample tables 
    and then this measure

    certificate total =
    VAR current_employee = SELECTEDVALUE(employee[employee])
    VAR total_cost =calculate( Sum(certificate[cost]),certificate[employee]=current_employee,all(certificate[cost]))
    RETURN total_cost
    Which i placed on a report
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      thank you kent 

       

      Unfortunately

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        just to let you know the column  Cost in my certificates table is a custom column which is Course time * Cours cost  

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi,

     

    Here is my test tables:

    Then create these columns:

    Course Time = DATEDIFF(Cost[Course Start Time],Cost[Course End Time],SECOND)/3600
    Cost = Cost[Course Time]*Cost[Course Cost]

    Please try this measure:

    Total Cost =
CALCULATE (
    AVERAGEX ( Cost, Cost[Course Cost] * Cost[Course Time] ),
    FILTER (
        ALLSELECTED ( Cost ),
        Cost[Certificates] IN FILTERS ( Cost[Certificates] )
    )
)

    The result shows:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi