Forum Discussion
Cost allocation
Hi Folks,
Got a fact table with purchase orders. Each PO is assigned to a product
Purchase orders;Amount;Product
#00001;500;TOTO
#00002;1500;TOTO
#00003;3000;TOTO
#00004;5000;TITI
Total Cost per product :
TOTO = 5000
TITI = 5000
Grand total = 10000
Got a dimension table.
Product;Software;Team
TOTO;TOTO EDITOR;TEAM01
TOTO;TOTO EDITOR;TEAM02
TOTO;TOTO READER;TEAM01
TITI;TITI;TEAM02
"Product" fields are linked
I'd like to allocate the product cost according the number of Team.
TOTO is used by 2 Teams (5000/2), TITI is used by one Team(5000/1).
I'd like to a get a the result in a simple table (Rows only)
Product;TEAM;Amount
TOTO; TEAM01;2500
TOTO; TEAM02;2500
TITI;TEAM02;5000
Grand Total : 10000
Calculation must be automaticaly updated (per line and grand total) If i use a segment to filter either Product, or Team
Filtered on TOTO
TOTO;TEAM01;2500
TOTO;TEAM02;2500
Grand total : 5000
Filtered on TITI
TITI;TEAM02;5000
Grand total : 5000
Filtered on TEAM01
TOTO;TEAM01;2500
Grand total : 2500
Filtered on TEAM02
TOTO;TEAM02;2500
TITI;TEAM02;500
Grand total : 7500
I Need the measure to calculate this. Could you please help me ?
I thank you in advance.
I thank you in advance.
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
8 Replies
- Juan-Power-biSuper User
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.
- Jay20024Helper I
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.
- johnt75Super User
You can create a measure like
Product Cost = SUMX( SUMMARIZE( 'Product', 'Product'[Product], 'Product'[Team] ), VAR NumTeams = COUNTROWS( CALCULATETABLE( VALUES( 'Product'[Team] ), REMOVEFILTERS( 'Product'[Team] ) ) ) VAR TotalCost = CALCULATE( SUM( 'Purchase Orders'[Amount] ) ) VAR Result = DIVIDE( TotalCost, NumTeams ) RETURN Result ) - FBergamaschiSuper User
Hello Jay20024
a relationship between the two tables would be a many to many, so I avoided that overall
Here are the tables I loaded, based on your sample
fact
dimension
Model (no relationship)
Attached the pbix I generated
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
- FBergamaschiSuper User
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
- Kedar_PandeSuper User
- v-pnaroju-msftCommunity Support
Thankyou, Juan-Power-bi, johnt75, FBergamaschi and Kedar_Pande for your responses.
Hi Jay20024,
We appreciate your inquiry through the Microsoft Fabric Community Forum.
We would like to inquire whether have you got the chance to check the solutions provided by Juan-Power-bi, johnt75, FBergamaschi and Kedar_Pandeto resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.
Thank you. - v-pnaroju-msftCommunity Support
Hi Jay20024,
We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.
Thank you.