Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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:
Hope this helps.
Best Regards,
Giotto Zhi
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:
Hope this helps.
Best Regards,
Giotto Zhi
I created some sample tables
and then this measure
Help when you know. Ask when you don't!
thank you kent so much for replaying
Unfortunately it's showing same thing does not sum up the cost. maybe i have to chnage the relationship from many to many to 1-many
just to let you know the column Cost in my certificates table is a custom column which is Course time * Cours cost
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |