cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Lowest Market Share with district name

9 REPLIES 9
Highlighted
Frequent Visitor

Re: Lowest Market Share with district name

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

Highlighted
Super User IV
Super User IV

Re: Lowest Market Share with district name

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."


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Community Champion
Community Champion

Re: Lowest Market Share with district name

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

Highlighted
Super User IV
Super User IV

Re: Lowest Market Share with district name

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/
Highlighted
Frequent Visitor

Re: Lowest Market Share with district name

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

Highlighted
Super User II
Super User II

Re: Lowest Market Share with district name

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


Highlighted
Community Champion
Community Champion

Re: Lowest Market Share with district name

@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
Highlighted
Frequent Visitor

Re: Lowest Market Share with district name

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

 

Regards

Harish Rathore

Highlighted
Community Champion
Community Champion

Re: Lowest Market Share with district name

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors