Moving cost from clients to company

[MadBern85]

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

[v-lionel-msft]

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.

[MadBern85]

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

[amitchandak · Accepted solution]

@MadBern85 , You can use isinscope

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[MadBern85]

@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?