Weighted Distribution of products accross stores

[thibbos]

Hi there,

First post here so thanks everyone for helping.

I need to calculate the weighted distribution (availability) of my products within the market.  For that we need:

STORE WEIGHT (independent of any product we could choose to filter):

• Total Market Sales = sales accross all stores and products for a given period (in this case L52W) => Measure is fine
• Total Store sales = Sales across that store for all products for a given period (in this case L52W) => measure is fine
• Divide both above measures to get the weight= >measure fine as well

=> First question: do you think it's best to get this as a measure or a calculated column (which I have now and showing in below measures)?

The weighted distribution is calculated as follows (V3 Retailer Code being the store):

•  Store sales for given period (in my case L16W = Last 16 weeks)
•  If above measure is greater than 0 =>Count 1
  •  Count Outlet Sales L16W = Calculate(DISTINCTCOUNT('Total Sales'[V3 Retailer Code ]),DateSheet[L16W AP]="L16W",FILTER('Total Sales','Total Sales'[Weekly Sales Quantity]<>0))
•  Sum stores weight for each stores that has a COUNT = 1
  •  Test = SUMX(FILTER(VALUES(StoreInfo[V3 Retailer Code ]),[Count Outlet Sales L16W]=1),StoreInfo[Total Weight Outlet])

Unfortunately when doing this and applying PRODUCT FILTERS on the visual, the count is correct, but it adds to the weight other outlets that do not have a count =1 for that specific product.

I suspect because my Count is not referencing any product in particular or any relationship to my product table.

here's a visual of what is explained above. Expected result would be 0,20% (for the TEST measure) as a total.

Any help would be appreciated

Thanks a lot Thibault

[thibbos · Accepted solution]

Hi all,

It seemed like i had the solution all along.

STORE WEIGHT (independent of any product we could choose to filter):

• Total Market Sales = sales accross all stores and products for a given period (in this case L52W) => Measure is fine
• Total Store sales = Sales across that store for all products for a given period (in this case L52W) => measure is fine
• Divide both above measures to get the weight= >measure fine as well

=> First question: do you think it's best to get this as a measure or a calculated column (which I have now and showing in below measures)?

1.Calculated columns for the weight based on timeline you wish is the way to go:

Total Weight Outlet =

var Outletsales = calculate(sum('Total Sales'[Weekly Sales Quantity]),DateSheet[L52W AP]="L52W",ALLEXCEPT('Total Sales','Total Sales'[V3 Retailer Code ]))

Return

divide(Outletsales,[TM SALES L52W])

2. Make new measure where you use your sale occurence timeline

TM Active Weighted Distribution P1 =

CALCULATE(sum(StoreInfo[Total Weight Outlet]),FILTER(DateSheet,DateSheet[P1]="P1"))

That was it, worked as a charm....

[MFelix]

Hi @thibbos ,

Be aware that DAX measure are based on context so any given value from slicers, filters, interactions with other visualizations, variables, relationships and so on can influence your result.

In this case I believe that the problem is exactly what you refer:

---

@thibbos wrote:

Unfortunately when doing this and applying PRODUCT FILTERS on the visual, the count is correct, but it adds to the weight other outlets that do not have a count =1 for that specific product.

I suspect because my Count is not referencing any product in particular or any relationship to my product table.

So basically you are missing the product on the formula.

In your information your data and data model are no explicit. 

Can you please share a mockup data or sample of your PBIX file and expected result. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[thibbos]

Hi felix,

Following your reply to my post, i indeed cnnot share the data itself with you ... it si quite sensitive, and even though i would trust you to use it properly, i just cannot do it. I'm looking at how i could change my data so that it wouldn't become sensitive.

Anyhow let me explain something else, I removed all my calculations has described in my post as I first wanted to share with you the following. I actually got the weighted distribution I need from two measures (using Greg keller Total of Sums post). Unfortunately, though the right result it takes around 10minutes to get the result for the 400ish products I have, which is not an accepatble solution. hence why i tried another way.

1. FIRST: here are the measures I use that work but takes very long time.

A: WeigtedDistribution TM = if([Count Outlet Sales L16W]>0,Sum(StoreInfo[Total Weight Outlet]))

WHERE COUNT OUTLET SALES => checks if there has been sales in the last 16Weeks)

TOTAL WEIGHT OUTLET => calculated column in my OUTLET(same as a store) table

B: A second measure I FOUND IN A POST TO MAKE IT WORK

WeightedDistribution TM Total =

VAR __Table = Summarize(StoreInfo,StoreInfo[V3 Retailer Code ],"__Total Weight Outlet",[WeigtedDistribution TM])

RETURN

IF(HASONEVALUE(StoreInfo[V3 Retailer Code ]),[WeigtedDistribution TM],sumx(__Table,[__Total Weight Outlet]))

I suspect that again, my COUNTSALES is the one creating too many checks.

Finally, i can maybe share a thing or two, but what would you need ?
1. I have a table with sales/date/product/store (4 columns)
2. I have a datesheet table
3. i have a product table (with all info)

1. I have a store table (with all info)

and thus by any (fastest mean possible) I need to caclulate the weighted dsitribution:

which equals = Sum fo weight of outlets (based on L52W sales of outlet/L52W Total market) which have a sale in the L16W.

tell me what you need, and if you really need the pbix (which is full of data/variables) i will try to do something

thanks a lot, best regards
Thibault

[MFelix]

In Hi @thibbos ,

I don't need the PBIX file has you have it, I need a mockup of your data probably not even all your data but enough information to get the calculation done.

In this case some mocukp of the sales information, product table and stores believe will be sufficient, but if you see that there are some other table and/or columns that you need for the calculation please add them.

Also if you are abble to provide accordingly to your mockup the final result would be great.