Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, somewhat new to PBI and still learning the ropes.
I have two measures concerning absence/illness connected to our clients and our company, one that calculates number of hours and one that calculates the cost. Like this:
AbsenceHours | AbsenceCost | |
Client 1 | 37,5 | 1500 |
Client 2 | 12 | 480 |
Client 3 | 40 | 1600 |
Company | 10 | 400 |
Total | 99,5 | 3980 |
Measures, if needed:
AbsenceHours = CALCULATE(SUM(Calculus [TotalHours]),FILTER(Hours,Hours[Type] = "Illness1 || Hours[Type] = "Illness2 || Hours[Type] = "Illness3 || Hours[Type] = "Illness4), FILTER('Salary', 'Salary' [SalaryCat] = "Paid"))
AbsenceCost = CALCULATE(SUM(Calculus[TotalCost]),FILTER(Hours,Hours[Type] = "Illness1 || Hours[Type] = "Illness2 || Hours[Type] = "Illness3 || Hours[Type] = "Illness4), FILTER('Salary', 'Salary' [SalaryCat] = "Paid"))
However, my goal is to only keep the number of hours at client level and have all the cost summarized under "Company".
Like this:
AbsenceHours | AbsenceCost | |
Client 1 | 37,5 | 0 |
Client 2 | 12 | 0 |
Client 3 | 40 | 0 |
Company | 10 | 3980 |
Total | 99,5 | 3980 |
I can't figure out how to get this done. Can anyone give me some direction?
Thanks in advance.
Regards,
Mads
Solved! Go to Solution.
@MadBern85 , You can use isinscope
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
Hi @MadBern85 ,
Please refer to my sample.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Lionel,
Thanks for the input, I'll check this out.
But I actually figured out the ISINSCOPE-solution and just implemented that.
Regards
Mads
@MadBern85 , You can use isinscope
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
@amitchandak , thanks for the tip, but I don't see the possible solution here with my current data model. ISINSCOPE seems to be dependent on hierarchies, and our clients and the company are on the same level in our database.
To get this to work, would I have to create a company column?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |