Share in shop where the product is available

[Anonymous]

Hi All,

I try to create a measure to calculate product share in shop where the product is available. Attach sample data with calculations.

Does anybody know the DAX code which will calculate such a thnig?

[Mikelytics · Accepted solution]

Hi @Anonymous 

Please find below a solution but I think your result for shop volumn where product is available | product C is wrong since product c is in shop 1,3 and 4. This emans total 3.100 - store 2 volume 300 = 2.800 and not 2.500 🙂

Base Data (Table called SampleShop)

Measure 1

Product Volume = SUM(SampleShop[Volume])

 Measure 2

Shop Volume where Product is available = 
var __Product = SELECTEDVALUE(SampleShop[Product])
var __ShopsWithProduct = 
    CALCULATETABLE(VALUES(SampleShop[Shop]),SampleShop[Product]=__Product)

RETURN
CALCULATE(
    SUMX(
        __ShopsWithProduct,
        CALCULATE([Product Volume])
    ),
    ALL(SampleShop)
)

Measure 3

Share in shop where product is available = 
DIVIDE(
    [Product Volume],
    [Shop Volume where Product is available]
)

Result

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Anonymous]

Thank you!! 🙂