Top 3 Average Sales mix

[GQ00]

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

[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 !!

[GQ00]

edited

[danextian]

Given your sample pbix, what is the expected result and why?

[GQ00]

edited

[danextian]

I'm confused. First your sample result does not match your sample pbix. Your pbix is looking at top2 but your result is top3 and total $ value without any filter is 815 bur your screenshots shows 162K. Anyway, try the attach pbix. Otherwise, please ensure your expected result uses the same data in your pbix.

[GQ00]

edited

[danextian]

Your expected result still doesnt match your sample pbix. Your expected result shows 162K but your pbix shows 815

[rajendraongole1 · Accepted solution]

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.

[GQ00]

edited

[v-karpurapud]

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.

[v-karpurapud]

Hi @GQ00 

It's been a while since we last heard from you. We are ready to assist you with resolving the issue, but we need the necessary details from you. Kindly share the information required so we can better understand and address your issue.

Thank You.

[GQ00]

edited

[v-aatheeque]

Hi @GQ00 ,

Could you please sharing some sample data and expected outputs, so that it could be easy for understand and give you best possible solution to resolve your issue.

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Please show the expected outcome based on the sample data you provided.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community