Forum Discussion
Helper IIITop 3 Average Sales mix
Hi,
I'm trying to highlight the revenue opportunity of aligning sales mix of certain products to top performing stores.
The fact salesitem table is related to tables such as Store, Product, Date
Store Tables includes AreaManager, ProductManager, StoreCohort (slicer).
I managed to get
1. Sales by Product: Sales=sum(SalesItem[SaleItem])
2. Sales by product (all): SalesAll=Calculate ([SalesbyProduct], all(Product))
3. Sales Mix %: Mix%=Divide ([SalesbyProduct],[SalesbyALLProduct],0)
I'm fine with these 3 changing by filter context, getting the following measures is where I'm struggling.
4. Top3=Top performing Stores mix:
averagex(
TOPN(3,
ALLSELECTED(Store),
[Mix%],0),
[Mix%] )
5. Opportunity Mix: Opp=The idea is to calculate the gap of the current sales mix, vs top 3 stores sales mix. Then multiply this by store sales. Meaning, if sales mix where to increase to top performing stores, then $x can be expected to be attained
SUMX(VALUES(Store), ([Top3]-[Mix%])*SalesAll)
*Ideally it would include an If Top3>Mix% then calculate otherwise blank, but I didn't know how to get it in there.
The Top3 measure seems to be producing the correct amount, but it adds extra rows when Matrix is under Area Manager rows
The Opp is clearly incorrect. Opportunity should be calculated at a store level
Please note Stores have only one Regional Manager and one Product Manager; but these can be assigned independently (see picture below)
I want to be able to show the $ Opportunity in a matrix that includes
Country, Area Manager, Product Manager, Store Name fields.
If the breakdown between Area and Product managers adds complexity to DAX, I'm happy to rely on parameters for swapping these.
I was expecting not to have extra blank rows, and the opportunity needs to be calculated iteratively at a store name level.
Matrix at store level is also incorrect, where opportunity is not correctly calculated
Can any kind soul help me troubleshoot these two measures? They have been giving me a headache for 2 days straight
Thanks of much
Hi GQ00 - your logic is correct conceptually, but the measures are being evaluated at Area Manager / Product Manager levels, not strictly at Store level.
Top3 Mix =
AVERAGEX(
TOPN(
3,
ALLSELECTED(Store[Store]),
[Mix %],
DESC
),
CALCULATE([Mix %])
)use the above, ensures ranking happens only by Store, try the above logic,
Hope this give the right result and check , still if any pls shae some sample data to test it.
14 Replies
- rajendraongole1
Super User
Hi GQ00 - your logic is correct conceptually, but the measures are being evaluated at Area Manager / Product Manager levels, not strictly at Store level.
Top3 Mix =
AVERAGEX(
TOPN(
3,
ALLSELECTED(Store[Store]),
[Mix %],
DESC
),
CALCULATE([Mix %])
)use the above, ensures ranking happens only by Store, try the above logic,
Hope this give the right result and check , still if any pls shae some sample data to test it.
GQ00edited
- v-karpurapud
Community Support
Hi GQ00
We have not received an update from you for some time. To assist you in resolving the issue, we kindly request that you provide the necessary details. Once we have this information, we will be able to address your concern effectively.
Thank you.
- GQ00
edited
- GQ00
edited
- v-aatheeque
Hi GQ00
Thank you for sharing the update. I'm glad to hear that the issue is now resolved.If any of the provided solution helped address your scenario, could you please mark or acknowledge the helpful response? This will make it easier for other community members who encounter a similar issue to quickly identify the working solution.
Your confirmation would be greatly appreciated and helpful for the community !!
