Allocate Monthly Cost Proportionally to Monthly Product Sales Amount considering customer And catego

[VazhaPBI]

Hello Friends,

need help in allocating monthly costs by customer and product category, to monthly sales by customer, product category and product, proportionally to sales amount of month, I have two tables one for sales, second for costs:

Sales Table:

Costs Table:

also i have result column in table showing how it has to be distributed.

Thanks a lot !

[MohammadLoran25 · Accepted solution]

Hi @VazhaPBI ,

1-

TotalSalesPerContext =
CALCULATE (
    SUM ( 'Sales Table'[Sales Amount] ),
    FILTER (
        ALL ( 'Sales Table' ),
        'Sales Table'[Customer] = SELECTEDVALUE ( 'Sales Table'[Customer] )
            && 'Sales Table'[Product Category]
                = SELECTEDVALUE ( 'Sales Table'[Product Category] )
            && 'Sales Table'[Month] = SELECTEDVALUE ( 'Sales Table'[Month] )
    )
)

2-

TotalCostPerContext =
CALCULATE (
    SUM ( 'Costs Table'[Cost] ),
    FILTER (
        ALL ( 'Costs Table' ),
        'Costs Table'[Customer] = SELECTEDVALUE ( 'Sales Table'[Customer] )
            && 'Costs Table'[Product Category]
                = SELECTEDVALUE ( 'Sales Table'[Product Category] )
            && 'Costs Table'[Month] = SELECTEDVALUE ( 'Sales Table'[Month] )
    )
)

3-

AllocatedCost =
[TotalCostPerContext]
    * DIVIDE ( SUM ( 'Sales Table'[Sales Amount] ), [TotalSalesPerContext] )

It helped? Mark it as an accepted solution.
Regards,
Loran

[BiNavPete · Accepted solution]

Hi
I would manage this in the Power Query Piece

Take a copy of the sales table by referencing the sales table - call it table A
Then merge with costs table on Month Customer and Product Category, selecting costs.
This will put total costs correctly onto each row.

With another reference of the Sales table, prep a table grouping Month,Customer and Product category, sum on Sales. Call it Table B.

Go back to table A and merge with table B to bring in Total sales.

Then simple add column Total Cost/Total Sales * Sales and boom.

Sample PBIX here.

Cheers

Pete

[VazhaPBI]

Thank You Pete It helped 100%

[MohammadLoran25 · Accepted solution]

Hi @VazhaPBI ,

1-

TotalSalesPerContext =
CALCULATE (
    SUM ( 'Sales Table'[Sales Amount] ),
    FILTER (
        ALL ( 'Sales Table' ),
        'Sales Table'[Customer] = SELECTEDVALUE ( 'Sales Table'[Customer] )
            && 'Sales Table'[Product Category]
                = SELECTEDVALUE ( 'Sales Table'[Product Category] )
            && 'Sales Table'[Month] = SELECTEDVALUE ( 'Sales Table'[Month] )
    )
)

2-

TotalCostPerContext =
CALCULATE (
    SUM ( 'Costs Table'[Cost] ),
    FILTER (
        ALL ( 'Costs Table' ),
        'Costs Table'[Customer] = SELECTEDVALUE ( 'Sales Table'[Customer] )
            && 'Costs Table'[Product Category]
                = SELECTEDVALUE ( 'Sales Table'[Product Category] )
            && 'Costs Table'[Month] = SELECTEDVALUE ( 'Sales Table'[Month] )
    )
)

3-

AllocatedCost =
[TotalCostPerContext]
    * DIVIDE ( SUM ( 'Sales Table'[Sales Amount] ), [TotalSalesPerContext] )

It helped? Mark it as an accepted solution.
Regards,
Loran

[VazhaPBI]

Thank you it was also helpful it worked i made slight change.