Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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
    )

2 REPLIES 2
v-luwang-msft
Community Support
Community Support

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
Super User
Super User

@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]))

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.