cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HarishRathore25
Helper I
Helper I

Lowest Market Share with district name

Hi,

I have following data where i need to find lowest market share of my company in different segment and district name. I am able to find lowest market share using MINX but i am not able to extract district name where market share is lowest. For example : What is lowest market share of Brand "ABC" in "Deluxe Shampoo" and what is the district name?

DistrictBrand NameCategoryCompanySegmentVolume
JaipurABCShampooMy CompanyDeluxe Shampoo100
JaipurDEFShampooCompany2Deluxe Shampoo50
JaipurGHIShampooCompany3Deluxe Shampoo40
JaipurJKLShampooCompany4Deluxe Shampoo120
AjmerABCShampooMy CompanyDeluxe Shampoo90
AjmerDEFShampooCompany2Deluxe Shampoo130
AjmerGHIShampooCompany3Deluxe Shampoo70
AjmerJKLShampooCompany4Deluxe Shampoo55
UdaipurABCShampooMy CompanyDeluxe Shampoo77
UdaipurDEFShampooCompany2Deluxe Shampoo35
UdaipurGHIShampooCompany3Deluxe Shampoo120
UdaipurJKLShampooCompany4Deluxe Shampoo98
JodhpurABCShampooMy CompanyDeluxe Shampoo80
JodhpurDEFShampooCompany2Deluxe Shampoo120
JodhpurGHIShampooCompany3Deluxe Shampoo95
JodhpurJKLShampooCompany4Deluxe Shampoo130
JaipurMNOShampooMy CompanyPremium Shampoo60
JaipurPQRShampooCompany2Premium Shampoo45
JaipurSTUShampooCompany3Premium Shampoo40
JaipurXYZShampooCompany4Premium Shampoo90
AjmerMNOShampooMy CompanyPremium Shampoo46
AjmerPQRShampooCompany2Premium Shampoo55
AjmerSTUShampooCompany3Premium Shampoo30
AjmerXYZShampooCompany4Premium Shampoo35
UdaipurMNOShampooMy CompanyPremium Shampoo22
UdaipurPQRShampooCompany2Premium Shampoo55
UdaipurSTUShampooCompany3Premium Shampoo15
UdaipurXYZShampooCompany4Premium Shampoo60
JodhpurMNOShampooMy CompanyPremium Shampoo45
JodhpurPQRShampooCompany2Premium Shampoo60
JodhpurSTUShampooCompany3Premium Shampoo70
JodhpurXYZShampooCompany4Premium Shampoo20
JaipurABCDShampooMy CompanyPrestige Shampoo120
JaipurDEFGShampooCompany2Prestige Shampoo50
JaipurGHIKShampooCompany3Prestige Shampoo40
JaipurJKLMShampooCompany4Prestige Shampoo90
AjmerABCDShampooMy CompanyPrestige Shampoo70
AjmerDEFGShampooCompany2Prestige Shampoo10
AjmerGHIKShampooCompany3Prestige Shampoo30
AjmerJKLMShampooCompany4Prestige Shampoo60
UdaipurABCDShampooMy CompanyPrestige Shampoo80
UdaipurDEFGShampooCompany2Prestige Shampoo45
UdaipurGHIKShampooCompany3Prestige Shampoo35
UdaipurJKLMShampooCompany4Prestige Shampoo75
JodhpurABCDShampooMy CompanyPrestige Shampoo130
JodhpurDEFGShampooCompany2Prestige Shampoo55
JodhpurGHIKShampooCompany3Prestige Shampoo45
JodhpurJKLMShampooCompany4Prestige Shampoo95

 

Request 2 different measures so that i can show in my summary data. Thanks in advance.

 

Regards

Harish Rathore

1 ACCEPTED SOLUTION

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

Based on the table that you have shared, please show the exact result you are expecting.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
harshnathani
Community Champion
Community Champion

Hi @HarishRathore25 ,

 

You can try these measures.

 

Lowest Market Share ( Brand, Segment, Town ) =
    MAXX (
        TOPN (
            1,
            SUMMARIZE (
                Data,
                Data[Brand Name],
                Data[Segment],
                Data[Town Name],
                "MS",
                CALCULATE (
                   [Market share],
                    ALLEXCEPT ( Data, Data[Brand Name], Data[Segment], Data[Town Name] )
                )
            ),
           [MS],
            ASC
        ),
       [MS]
    )

 

Town Name Lowest Market Share ( Brand, Segment, Town ) =
    MAXX (
        TOPN (
            1,
            SUMMARIZE (
                Data,
                Data[Brand Name],
                Data[Segment],
                Data[Town Name],
                "MS",
                CALCULATE (
                   [Market share],
                    ALLEXCEPT ( Data, Data[Brand Name], Data[Segment], Data[Town Name] )
                )
            ),
           [MS],
            ASC
        ),
        Data[Town Name]
    )

 

 

1.jpg

 

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

Hi @harshnathani , This is what i want exactly but my only problem is that I have town master and product master table seperately which are in relationship with fact table. Can I still use summarize function? I guess not. Please help.

 

Regards

@HarishRathore25 ,

 

It will work if it is linked through a relationship.

 

 

1.jpg

 

Have created a Town Master Table and seems to work fine.

 

 

Lowest Market Share (Brand,Segment,Town) =
MAXX(
TOPN(1,
 
SUMMARIZE(
Data,
Data[Brand Name],Data[Segment],'Town-Master'[Town Name],
"MS" , CALCULATE([Market share], ALLEXCEPT(Data,Data[Brand Name],Data[Segment],'Town-Master'[Town Name]))
),
[MS],
ASC
),
[MS]
)
 
 
Regards,
Harsh Nathani

Hi @harshnathani , Can you please share file? it would be great help.

 

Regards

Harish Rathore

Hi @HarishRathore25 ,

 

 

https://we.tl/t-RaWBgVaUu7

 

Regards,

Harsh Nathani

I may be misunderstanding what you need. I loaded your sample data and extracted a "Brands" table. Then I was able to write a measure that can find the district that has the lowest volume for each brand.
Brand.PNG
The trick, I think. Is to use a couple of VAR statements in the measure to "remember" the brand and the lowest volume, and then to use those values to look up the district name.

Low Market Share District =
VAR vBrand = selectedvalue(Brands[Brand Name])
VAR vMin_amount = CALCULATE(min(MarketShare[Volume]),ALL(MarketShare),MarketShare[Brand Name]=vBrand)
VAR vDistrict = CALCULATE(MIN(MarketShare[District]),MarketShare[Brand Name] = vBrand, MarketShare[Volume] = vMin_amount)
RETURN vDistrict




Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Greg_Deckler
Super User
Super User

You want Lookup Min/Max:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

 

It is built for the following circumstance:

"I have this table of data with these items in it and values for various blah. I need to create a measure that returns which item has the lowest/highest sum/average/blah."


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition
HarishRathore25
Helper I
Helper I

You may download pbix file where @Ashish_Mathur  had solved one of my query. Data is the same but there are few measures already created.

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Market-Share-in-Respective-Category/m-p/10359...

 

Regards

Harish Rathore

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors