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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors