DAX : Compare Market Share with average market share of top N

[Anonymous]

Hello! Could you please make an advice on my issue.

I have table with following dimensions : Year, Product, Sector

For each combination I have MarketSize and Sales

So I'm computing market share (Sales/MarketSize)

Now I want to add columns to my table, for example Average Market Share of top10 Sectors and I'm stuck on that stage, because, with my dax I can only have the values for  top 10 sectors and others are blank.

Ideally I want to say what sales I could have if my sectors  have at least this average MarketShare and put sectors' growth potential (-1+ideal sales/current sales) on the graph.

Thank you!!

My dax below:

AvgMarketShareTop10 =
VAR curryear = 'Table'[YEAR]
VAR prod = 'Table'[Product]
VAR totshare = 'Table'[MarketShare]
VAR ranking =
    COUNTROWS (
        FILTER (
            ALL ( 'Table' ),
            'Table'[YEAR] = curryear
                && 'Table'[Product] = prod
                && 'Table'[MarketShare] > totshare
        )
    ) + 1
RETURN
    CALCULATE (
        DIVIDE (
            SUMX (
                SUMMARIZE ( 'Table', 'Table'[Sector], 'Table'[YEAR], 'Table'[Sales] ),
                'Table'[Sales]
            ),
            ( MarketSize )
        ),
        ranking <= 10
    )

[v-luwang-msft]

Hi @Anonymous ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[amitchandak]

@Anonymous , You need to have measure like

Top10 Date =CALCULATE(averagex(Values(Table[Sector]), [MarketSize]) ,TOPN(10,allselected(Table[Sector]),[MarketSize] ,DESC),VALUES(Table[Sector]))