Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
District | Brand Name | Category | Company | Segment | Volume |
Jaipur | ABC | Shampoo | My Company | Deluxe Shampoo | 100 |
Jaipur | DEF | Shampoo | Company2 | Deluxe Shampoo | 50 |
Jaipur | GHI | Shampoo | Company3 | Deluxe Shampoo | 40 |
Jaipur | JKL | Shampoo | Company4 | Deluxe Shampoo | 120 |
Ajmer | ABC | Shampoo | My Company | Deluxe Shampoo | 90 |
Ajmer | DEF | Shampoo | Company2 | Deluxe Shampoo | 130 |
Ajmer | GHI | Shampoo | Company3 | Deluxe Shampoo | 70 |
Ajmer | JKL | Shampoo | Company4 | Deluxe Shampoo | 55 |
Udaipur | ABC | Shampoo | My Company | Deluxe Shampoo | 77 |
Udaipur | DEF | Shampoo | Company2 | Deluxe Shampoo | 35 |
Udaipur | GHI | Shampoo | Company3 | Deluxe Shampoo | 120 |
Udaipur | JKL | Shampoo | Company4 | Deluxe Shampoo | 98 |
Jodhpur | ABC | Shampoo | My Company | Deluxe Shampoo | 80 |
Jodhpur | DEF | Shampoo | Company2 | Deluxe Shampoo | 120 |
Jodhpur | GHI | Shampoo | Company3 | Deluxe Shampoo | 95 |
Jodhpur | JKL | Shampoo | Company4 | Deluxe Shampoo | 130 |
Jaipur | MNO | Shampoo | My Company | Premium Shampoo | 60 |
Jaipur | PQR | Shampoo | Company2 | Premium Shampoo | 45 |
Jaipur | STU | Shampoo | Company3 | Premium Shampoo | 40 |
Jaipur | XYZ | Shampoo | Company4 | Premium Shampoo | 90 |
Ajmer | MNO | Shampoo | My Company | Premium Shampoo | 46 |
Ajmer | PQR | Shampoo | Company2 | Premium Shampoo | 55 |
Ajmer | STU | Shampoo | Company3 | Premium Shampoo | 30 |
Ajmer | XYZ | Shampoo | Company4 | Premium Shampoo | 35 |
Udaipur | MNO | Shampoo | My Company | Premium Shampoo | 22 |
Udaipur | PQR | Shampoo | Company2 | Premium Shampoo | 55 |
Udaipur | STU | Shampoo | Company3 | Premium Shampoo | 15 |
Udaipur | XYZ | Shampoo | Company4 | Premium Shampoo | 60 |
Jodhpur | MNO | Shampoo | My Company | Premium Shampoo | 45 |
Jodhpur | PQR | Shampoo | Company2 | Premium Shampoo | 60 |
Jodhpur | STU | Shampoo | Company3 | Premium Shampoo | 70 |
Jodhpur | XYZ | Shampoo | Company4 | Premium Shampoo | 20 |
Jaipur | ABCD | Shampoo | My Company | Prestige Shampoo | 120 |
Jaipur | DEFG | Shampoo | Company2 | Prestige Shampoo | 50 |
Jaipur | GHIK | Shampoo | Company3 | Prestige Shampoo | 40 |
Jaipur | JKLM | Shampoo | Company4 | Prestige Shampoo | 90 |
Ajmer | ABCD | Shampoo | My Company | Prestige Shampoo | 70 |
Ajmer | DEFG | Shampoo | Company2 | Prestige Shampoo | 10 |
Ajmer | GHIK | Shampoo | Company3 | Prestige Shampoo | 30 |
Ajmer | JKLM | Shampoo | Company4 | Prestige Shampoo | 60 |
Udaipur | ABCD | Shampoo | My Company | Prestige Shampoo | 80 |
Udaipur | DEFG | Shampoo | Company2 | Prestige Shampoo | 45 |
Udaipur | GHIK | Shampoo | Company3 | Prestige Shampoo | 35 |
Udaipur | JKLM | Shampoo | Company4 | Prestige Shampoo | 75 |
Jodhpur | ABCD | Shampoo | My Company | Prestige Shampoo | 130 |
Jodhpur | DEFG | Shampoo | Company2 | Prestige Shampoo | 55 |
Jodhpur | GHIK | Shampoo | Company3 | Prestige Shampoo | 45 |
Jodhpur | JKLM | Shampoo | Company4 | Prestige Shampoo | 95 |
Request 2 different measures so that i can show in my summary data. Thanks in advance.
Regards
Harish Rathore
Solved! Go to Solution.
Hi,
Based on the table that you have shared, please show the exact result you are expecting.
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] )
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
It will work if it is linked through a relationship.
Have created a Town Master Table and seems to work fine.
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.
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.
Help when you know. Ask when you don't!
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."
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.
Regards
Harish Rathore
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
19 | |
16 | |
16 | |
16 |
User | Count |
---|---|
51 | |
23 | |
20 | |
17 | |
16 |