cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aqavi10
Advocate I
Advocate I

Top value in a category based on 2 columns

Hi everyone,

 

  Can someone please help me accomplish this task below? I need to display the store with the highest sales in a 'Card' visual. This needs to be categorized based on the 'State' and the 'Year'. Ultimately, I would want to slice by 'Year' and 'State' and the 'Card' visual should display the store with highest sales in that 'State' in that 'Year'. 

 

The data below has sales for 2018 & 2019 and is a sample for a few stores. I have tried to create a calculated table where after using the Rank & Filter function , I was able to get a table with top rank/'Rank1' stores(ranked by sales) for each state in 2018 & 2019. But I'm unable to connect it with the main data table since it is a many to many relationship and the keys to connect would be 'Year' &'State' but it would only use 1 relationship to connect.

 

Thank you

 

WeekYearMonth StoreNameStateTotalDollarSales
112018March Store Wynnum WestQueensland10239.24
112018MarchStore MaroochydoreQueensland11972.33
112018MarchStore BairnsdaleVictoria12706.75
112018MarchStore Mosman ParkWestern Australia5730.57
112018MarchStore BeenleighQueensland11705.45
112018MarchStore ArmadaleWestern Australia10301.74
112018MarchStore Belmont Forum (Rebadge 368)Western Australia17674.32
112018MarchStore FindonSouth Australia12890.7
112018MarchStore KatoombaNew South Wales9662.39
112018MarchStore CamdenNew South Wales8156.99
112018MarchStore Kurri KurriNew South Wales19059.99
112018MarchStore Bay City (Geelong)Victoria5772.15
112018MarchStore Bondi Junction EastgateNew South Wales12090.18
112018MarchStore Port Adelaide Church StreetSouth Australia17561.86
112018MarchStore PalmerstonNorthern Territory10243.56
112018MarchStore BraybrookVictoria12917.27
112018MarchStore KaleenAustralian Capital Territory6310.24
112018MarchStore ChisholmAustralian Capital Territory7865.25
112018MarchStore WaniassaAustralian Capital Territory7469.3
112018MarchStore Alexander HeightsWestern Australia12050.61
112018MarchStore LutwycheQueensland8639.26
112018MarchStore ParalowieSouth Australia9039.99
112018MarchStore EngadineNew South Wales8723.82
112018MarchStore Ferntree GullyVictoria7688.41
112018MarchStore Fairfield (QLD)Queensland8118.32
112018MarchStore Dandenong PlazaVictoria15150.77
112018MarchStore Mount Barker (Cameron Street)South Australia14722.16
112018MarchStore Parramatta NorthNew South Wales11126.07
112018MarchStore Wattle GroveNew South Wales12526.99
112018MarchStore MowbrayTasmania9864.16
112018MarchStore DevonportTasmania16066.54
112019MarchStore Wynnum WestQueensland10739.24
112019MarchStore MaroochydoreQueensland12472.33
112019MarchStore BairnsdaleVictoria13456.75
112019MarchStore Mosman ParkWestern Australia6230.57
112019MarchStore BeenleighQueensland12205.45
112019MarchStore ArmadaleWestern Australia10801.74
112019MarchStore Belmont Forum (Rebadge 368)Western Australia18174.32
112019MarchStore FindonSouth Australia13390.7
112019MarchStore KatoombaNew South Wales10162.39
112019MarchStore CamdenNew South Wales8656.99
112019MarchStore Kurri KurriNew South Wales19559.99
112019MarchStore Bay City (Geelong)Victoria6272.15
112019MarchStore Bondi Junction EastgateNew South Wales12590.18
112019MarchStore Port Adelaide Church StreetSouth Australia18061.86
112019MarchStore PalmerstonNorthern Territory10743.56
112019MarchStore BraybrookVictoria13417.27
112019MarchStore KaleenAustralian Capital Territory6810.24
112018MarchStore ChisholmAustralian Capital Territory9853.25
112018MarchStore WaniassaAustralian Capital Territory6933.52
112019MarchStore Alexander HeightsWestern Australia12550.61
112019MarchStore LutwycheQueensland9139.26
112019MarchStore ParalowieSouth Australia9539.99
112019MarchStore EngadineNew South Wales9223.82
112019MarchStore Ferntree GullyVictoria8188.41
112019MarchStore Fairfield (QLD)Queensland8618.32
112019MarchStore Dandenong PlazaVictoria15650.77
112019MarchStore Mount Barker (Cameron Street)South Australia15222.16
112019MarchStore Parramatta NorthNew South Wales11626.07
112019MarchStore Wattle GroveNew South Wales13026.99
112019MarchStore MowbrayTasmania10364.16
112019MarchStore DevonportTasmania16566.54

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

13 REPLIES 13
Ashish_Mathur
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

Thanks a ton @Ashish_Mathur for spending your valuable time on this!

The measure was perfectly written. You're a true gem.

You are welcome.  Thank you for your kind words.


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

Hi Ashish, 

 

  This is regarding your solution yesterday.    It doesn't show the right store as per the highest sales. I've included more data in this sample , however it doesn't reflect the right store.  

Thank you

 

Hi,

What is the problem?  My answer is correct.  See the screenshot below:

Untitled.png


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

Hi Ashish,

 

  The answer is absolutely correct for total dollar sales when checked per year. Apologies in conveying the 2nd question aptly! However, if I wanted to show the highest earning store by week on the card and not the sum of sales for the whole year for any store , how would I be able to do it?

 

Example:

In the dataset shared, highest earning store by week for Tasmania is Store Devonport in 2019 and Store Northgate in 2018.  

 

Thanks again for the help! 

Cheers 🙂

Hi,

I am still confused.  You can create a slicer for Week as well and select any one week.


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

Hi Ashish,

 

  Apologies for the confusion. I meant, if we wanted the visual card to display the store which has earned highest in a particular week in the year selected. Basically, the ideal output would be the card displaying the store name/week number/amount(highest selling week) without the need to slice by week number. As of now, with the current solution, I'm able to display the amount rightly on a card for the highest selling store (in a week) but not the store .

 

 

pbi.png

 

 

 

Hi,

I am not sure of how much I can help but I would like to try.  Share your PBI file with data for a few weeks.  In your previous posts, your file had data only for one week.


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

Hi Ashish,

 

   The data file shared before has a table named as 'Sample' . I reckon, that should be useful with multiple weeks of data.

 

 

Lastly, thanks again for looking into this.

 

 

Hi,

That takes me to a sign-in page.


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

Hi,

See if this measure works

Store(s) with highest sales = CONCATENATEX(TOPN(1,GENERATE(VALUES(Data[StoreName]),VALUES(Data[Week])),[Revenue]),Data[StoreName],", ")

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors