topic Re: Top 3 Average Sales mix in Desktop

Top 3 Average Sales mix

GQ00 — Thu, 26 Feb 2026 01:05:32 GMT

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

Re: Top 3 Average Sales mix

rajendraongole1 — Thu, 26 Feb 2026 04:31:20 GMT

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.

Re: Top 3 Average Sales mix

GQ00 — Fri, 27 Mar 2026 04:01:02 GMT

edited

Re: Top 3 Average Sales mix

v-karpurapud — Mon, 02 Mar 2026 06:37:13 GMT

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.

Re: Top 3 Average Sales mix

v-karpurapud — Thu, 05 Mar 2026 10:51:48 GMT

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.

Re: Top 3 Average Sales mix

GQ00 — Fri, 27 Mar 2026 04:01:12 GMT

edited

Re: Top 3 Average Sales mix

v-aatheeque — Tue, 10 Mar 2026 05:37:00 GMT

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

Re: Top 3 Average Sales mix

GQ00 — Fri, 27 Mar 2026 04:01:24 GMT

edited

Re: Top 3 Average Sales mix

danextian — Wed, 11 Mar 2026 05:22:16 GMT

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

Re: Top 3 Average Sales mix

GQ00 — Fri, 27 Mar 2026 04:01:32 GMT

edited

Re: Top 3 Average Sales mix

danextian — Thu, 12 Mar 2026 11:19:19 GMT

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.

Re: Top 3 Average Sales mix

GQ00 — Fri, 27 Mar 2026 04:01:38 GMT

edited

Re: Top 3 Average Sales mix

danextian — Sat, 14 Mar 2026 11:44:17 GMT

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

Re: Top 3 Average Sales mix

GQ00 — Fri, 27 Mar 2026 04:01:44 GMT

edited

Re: Top 3 Average Sales mix

v-aatheeque — Mon, 16 Mar 2026 04:23:46 GMT

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