Cost Per Unit Won't Drill Down to Account-Level in a Matrix

[minimumlives]

Hi, I am calculating a cost per unit, which was easily done using a basic measure. However, I've been requested to add a drill down to the Cost Per Unit at the account level, and instead of displaying properly, the drill down seems to show all data, and not on a per unit basis.

Here is the data:

CompanyName	AcctDescr	Category	Value
A	Paint	Maint	45396
A	Floors	Maint	4000
A	Carpet	Maint	6000
A	Unit Count	Units	200
B	Paint	Maint	54880
B	Floors	Maint	4000
B	Carpet	Maint	1000
B	Unit Count	Units	400
C	Paint	Maint	67996
C	Floors	Maint	4000
C	Carpet	Maint	700
C	Unit Count	Units	500

Here is what I'm hoping to show in a PBI table or matrix: a total and a drill down to the cost account level.

Thanks!

[littlemojopuppy · Accepted solution]

Hi @minimumlives 

The problem is that you have two different things in your data: costs and units.  You need to split them into separate tables to get this to work correctly.

The attached Excel file has your solution.  The DAX for cost/unit is simply

Cost / Unit:=
DIVIDE(
	SUM(RawData[Value]),
	SUM(Companies[Units]),
	BLANK()
)

Here's the results

Hope this helps!

[minimumlives]

It works. Thank you so much.  

[littlemojopuppy]

@minimumlives you're welcome.  Glad I could help!