Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
v-gizhi-msft
Community Support
Community Support

Hi,

 

Here is my test tables:

50.PNG51.PNG

2.jpg

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:

52.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto Zhi

View solution in original post

4 REPLIES 4
v-gizhi-msft
Community Support
Community Support

Hi,

 

Here is my test tables:

50.PNG51.PNG

2.jpg

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:

52.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto Zhi

kentyler
Solution Sage
Solution Sage

I created some sample tables employee_certificate.PNG
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
2tables.PNG




Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Anonymous
Not applicable

thank you kent 

 

Unfortunately

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.