How to break down monthly product cost per sales day?

[jerpic]

Hello Community,

I need your help and your expertise to solve my problem.

I have two tables SALES and PRODUCTS.

The PRODUCTS table contains the products list and their monthly cost.

The second table represents the sales per day.

I would like to see a new column "COST" in the SALES table wich contains for each day the break down of the montly cost related to the product.

For instance, regarding the PRODUCT-1 in april, that product was sold 3 times, there for each of 3 days the cost should be 10 000/3. 

For the PRODUCT-3 in april, we have only 1 day of sale, so the COST will be 30 000.

Do you know how I can do that? Any help will be very much appreciated.

Best Regards,

Jérôme.

[Anonymous · Accepted solution]

@jerpic Solution may be like this:

COST=var monthlyCost=LOOKUPVALUE('PRODUCTS'[MONTHLY COST],'PRODUCTS'[PRODUCTS],'SALES'[PRODUCTS])

VAR CurrentProduct='SALES'[PRODUCTS]

VAR ProductCount=COUNTROWS(FILTER('SALES','SALES'[PRODUCTS]=CurrentProduct))

RETURN DIVIDE(MonthlyCost,ProductCount)

[jerpic]

Many thanks @Anonymous !!! This is exactly what I needed.

I have juste added a condition on the date in the FILTER command, so the final code is :

COST = 

var monthlyCost=LOOKUPVALUE('PRODUITS'[CHARGE A MENSUEL],'PRODUITS'[PRODUITS],'VENTES'[PRODUITS])

VAR CurrentProduct='VENTES'[PRODUITS]
VAR CurrentMonth = RELATED(Dim_Calendrier[Year-Month])
VAR ProductCount=COUNTROWS(FILTER('VENTES','VENTES'[PRODUITS]=CurrentProduct && FORMAT('VENTES'[DATE],"YYYY-MM")=CurrentMonth))

RETURN DIVIDE(MonthlyCost,ProductCount)

Best Regards, 

[jerpic]

Hello @amitchandak ,

Thanks for your link, It's quite similar excecpted the fact that I have to distribute the monthly cost per day (like you) and per product (day of sale), and there I'm lost.

Best Regards,

[v-kelly-msft]

Hi  @jerpic ,

First create a relationship between the 2 tables;

Then create 2 calculated columns as below：

Monthlycost = LOOKUPVALUE('PRODUCTS'[MONTHLY COST],'PRODUCTS'[PRODUCTS],'SALES'[PRODUCTS],0)

Cost =
VAR _count =
    CALCULATE (
        COUNTROWS ( 'SALES' ),
        FILTER (
            'SALES',
            MONTH ( 'SALES'[Date] ) = MONTH ( EARLIER ( SALES[Date] ) )
                && YEAR ( 'SALES'[Date] ) = YEAR ( EARLIER ( SALES[Date] ) )
                && 'SALES'[PRODUCTS] = EARLIER ( SALES[PRODUCTS] )
        )
    )
RETURN
    DIVIDE ( 'SALES'[Monthlycost], _count )

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[amitchandak]

@jerpic , refer if my blog on the same can help

Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-...