Forum Discussion

Jay20024's avatar
Jay20024
Helper I
5 months ago
Solved

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.

 

8 Replies

  • 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.

    • Jay20024's avatar
      Jay20024
      Helper 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.

  • 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
    )
  • 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

  • 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.

  • 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.