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?
|Jaipur||ABC||Shampoo||My Company||Deluxe Shampoo||100|
|Ajmer||ABC||Shampoo||My Company||Deluxe Shampoo||90|
|Udaipur||ABC||Shampoo||My Company||Deluxe Shampoo||77|
|Jodhpur||ABC||Shampoo||My Company||Deluxe Shampoo||80|
|Jaipur||MNO||Shampoo||My Company||Premium Shampoo||60|
|Ajmer||MNO||Shampoo||My Company||Premium Shampoo||46|
|Udaipur||MNO||Shampoo||My Company||Premium Shampoo||22|
|Jodhpur||MNO||Shampoo||My Company||Premium Shampoo||45|
|Jaipur||ABCD||Shampoo||My Company||Prestige Shampoo||120|
|Ajmer||ABCD||Shampoo||My Company||Prestige Shampoo||70|
|Udaipur||ABCD||Shampoo||My Company||Prestige Shampoo||80|
|Jodhpur||ABCD||Shampoo||My Company||Prestige Shampoo||130|
Request 2 different measures so that i can show in my summary data. Thanks in advance.
Solved! Go to Solution.
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.
You want Lookup Min/Max:
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."
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] )
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
Based on the table that you have shared, please show the exact result you are expecting.
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.
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.
It will work if it is linked through a relationship.
Have created a Town Master Table and seems to work fine.
It’s time for another PBI Community recap!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.
Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!