Forum Discussion
Cost allocation
A correction to my previous solution attempt, now all numbers match with what was requested.
I had overlooked a request on the filtering a single team
File attached and a couple of results here follow
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Good use case — this is a proportional cost allocation pattern. The key is that the divisor (number of teams per product) must be calculated ignoring the current team filter but respecting any product filter, so that the allocation ratio stays correct.
Here's the measure:
daxAllocated Cost =
VAR CurrentProduct = SELECTEDVALUE(Dimension[Product])
VAR CurrentTeam = SELECTEDVALUE(Dimension[Team])
-- Total cost for the current product (respects product filter, ignores team filter)
VAR TotalProductCost =
CALCULATE(
SUM(PurchaseOrders[Amount]),
REMOVEFILTERS(Dimension[Team])
)
-- Total number of DISTINCT teams for the current product (ignores team filter)
VAR TotalTeams =
CALCULATE(
DISTINCTCOUNT(Dimension[Team]),
REMOVEFILTERS(Dimension[Team])
)
RETURN
IF(
TotalTeams = 0,
BLANK(),
DIVIDE(TotalProductCost, TotalTeams)
)
Put this in a table visual with Product and Team from your dimension table as rows. The measure will automatically divide correctly in every filter context.
How it handles your scenarios:
Filtered on TOTO → both TEAM01 and TEAM02 rows show 2500, grand total 5000 ✓
Filtered on TEAM01 → only TOTO/TEAM01 row shows 2500 (TITI has no TEAM01), grand total 2500 ✓
Filtered on TEAM02 → TOTO/TEAM02 = 2500, TITI/TEAM02 = 5000, grand total 7500 ✓
One note: your dimension table has a row for TOTO;TOTO EDITOR;TEAM01 and TOTO;TOTO READER;TEAM01 — that's two rows for the same Product+Team combination. If you want to count unique teams (not unique software+team combinations), make sure DISTINCTCOUNT(Dimension[Team]) is counting the Team column specifically, which it is in the formula above. TOTO will still count as 2 distinct teams regardless of how many software rows exist.
Thanks Juan for your feedback. Works great for the allocation, but when i filter (or remove all filters) the grand total must be sum of displayed lines. When filter on TOTO → both TEAM01 and TEAM02 rows show 2500, grand total 2500 ✓ In any case, Filtered or not, I'd like the grand total must equal to the sum of all filtered displayed rows (by defaut, PBI only shows products that match both sides.