Measure to allocate freight per item

[helen_wrensted1]

Hi.

In my report, I have below tables:

In the Freight table, I only have the freight per supplier and not per item. 

I wish to allocate the freight costs to items based on the CBM per item from the Purchase table:

Alloc_Freight =

VAR SlutDato = EOMONTH ( TODAY(), -1 )

VAR StartDato = EOMONTH ( SlutDato, -12 ) + 1

VAR MaterialeCBM = CALCULATE ( SUM ( Purchase_Orders[CBM_Total] ),

DATESBETWEEN ( DimDate[FullDate], StartDato, SlutDato ) )

VAR TotalCBM_Supplier = CALCULATE ( SUM ( Purchase_Orders[CBM_Total] ),

DATESBETWEEN ( DimDate[FullDate], StartDato, SlutDato ),

REMOVEFILTERS ( Purchase_Orders[Materiale] ) )

VAR TotalFreight_Supplier = CALCULATE ( SUM ( Freight[BetragHW] ),

DATESBETWEEN ( DimDate[FullDate], StartDato, SlutDato ) )

RETURN DIVIDE ( MaterialeCBM * TotalFreight_Supplier, TotalCBM_Supplier )

As you can see, the freight charges are not allocated:

What am I doing wrong?

Thanks a lot in advance.

[v-tsaipranay · Accepted solution]

Hi @helen_wrensted1 ,

Yes, you should remove the filter from the Item dimension (MAT_EAN_DIMS).

Even though the Freight table itself does not contain Item, the Item filter originates from MAT_EAN_DIMS and propagates through the model, indirectly restricting the freight calculation.

REMOVEFILTERS ( MAT_EAN_DIMS )

You are explicitly clearing the Item context while keeping the Supplier context intact. This allows the measure to return the full supplier-level freight, which can then be allocated correctly to items based on CBM.

So this is the expected and correct pattern when allocating higher-grain facts (supplier-level freight) down to a lower-grain dimension (items).

Thank you.

[helen_wrensted1]

Hi @v-tsaipranay 

It worked! Thanks a lot for your help - it is highly appreciated.

[v-tsaipranay]

Hi @helen_wrensted1 ,

Thanks for confirming. Since your matrix rows come from Creditor (Supplier) and MAT_EAN_DIMS (Item), the Item filter is propagating into the freight calculation.

Because freight exists only at supplier level, TotalFreight_Supplier must ignore the Item context. You can fix this by explicitly removing the Item filter when calculating total freight.

This ensures the full supplier freight is returned and can then be allocated to items based on CBM.

If the issue persists, please also confirm there is an active relationship from Creditor to Freight.

Also thank you @FBergamaschi for helpful response.

Thank you for using fabric community forum.

[helen_wrensted1]

Hi @v-tsaipranay 

Thanks a lot for your reply. 

I do have an active relationship from Creditor to Freight. With regards to the remove item filter for Freight, should I use MAT_EAN_DIMS (Items) as I don't have item in the Freight data? Sorry for a perhaps stupid question.

[FBergamaschi]

Hi @helen_wrensted1,

you can change the variable to be returned, after commenting the DIVIDE call, and see which one stucks at 0/BLANK while it should not.

Which table do the column in grouped in rows in the matrix come from? (Supplier and Material? They are blanked out for obvious reasons but just to understand the filters applied)  And do you have filters / slicers around the matrix?

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

[helen_wrensted1]

Hi @FBergamaschi 

The columns in the matrix are from table Creditor (Supplier) and table MAT_EAN_DIMS (Item), so I am using the dimension tables.

It seems to be the VAR TotalFreightSupplier that is failing. Any suggestions how to fix it?

Thanks a lot.

[FBergamaschi]

Any way you can send me the file via private message? So there is no problem of confidentiality?

I need to check the pbix and shall fix in one minute, otherwise I can only guess that we need some REMOVEFILTERS there in that measure

If this helped, please consider giving kudos and mark as a solution

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

[helen_wrensted1]

Hi @FBergamaschi 

Thanks a lot for your effort - it is highly appeciated. Unfortunately, I am not bale to share the file with you, but I'll try to make a dummy file with similar data later.