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.
|11||2018||March||Store Wynnum West||Queensland||10239.24|
|11||2018||March||Store Mosman Park||Western Australia||5730.57|
|11||2018||March||Store Armadale||Western Australia||10301.74|
|11||2018||March||Store Belmont Forum (Rebadge 368)||Western Australia||17674.32|
|11||2018||March||Store Findon||South Australia||12890.7|
|11||2018||March||Store Katoomba||New South Wales||9662.39|
|11||2018||March||Store Camden||New South Wales||8156.99|
|11||2018||March||Store Kurri Kurri||New South Wales||19059.99|
|11||2018||March||Store Bay City (Geelong)||Victoria||5772.15|
|11||2018||March||Store Bondi Junction Eastgate||New South Wales||12090.18|
|11||2018||March||Store Port Adelaide Church Street||South Australia||17561.86|
|11||2018||March||Store Palmerston||Northern Territory||10243.56|
|11||2018||March||Store Kaleen||Australian Capital Territory||6310.24|
|11||2018||March||Store Chisholm||Australian Capital Territory||7865.25|
|11||2018||March||Store Waniassa||Australian Capital Territory||7469.3|
|11||2018||March||Store Alexander Heights||Western Australia||12050.61|
|11||2018||March||Store Paralowie||South Australia||9039.99|
|11||2018||March||Store Engadine||New South Wales||8723.82|
|11||2018||March||Store Ferntree Gully||Victoria||7688.41|
|11||2018||March||Store Fairfield (QLD)||Queensland||8118.32|
|11||2018||March||Store Dandenong Plaza||Victoria||15150.77|
|11||2018||March||Store Mount Barker (Cameron Street)||South Australia||14722.16|
|11||2018||March||Store Parramatta North||New South Wales||11126.07|
|11||2018||March||Store Wattle Grove||New South Wales||12526.99|
|11||2019||March||Store Wynnum West||Queensland||10739.24|
|11||2019||March||Store Mosman Park||Western Australia||6230.57|
|11||2019||March||Store Armadale||Western Australia||10801.74|
|11||2019||March||Store Belmont Forum (Rebadge 368)||Western Australia||18174.32|
|11||2019||March||Store Findon||South Australia||13390.7|
|11||2019||March||Store Katoomba||New South Wales||10162.39|
|11||2019||March||Store Camden||New South Wales||8656.99|
|11||2019||March||Store Kurri Kurri||New South Wales||19559.99|
|11||2019||March||Store Bay City (Geelong)||Victoria||6272.15|
|11||2019||March||Store Bondi Junction Eastgate||New South Wales||12590.18|
|11||2019||March||Store Port Adelaide Church Street||South Australia||18061.86|
|11||2019||March||Store Palmerston||Northern Territory||10743.56|
|11||2019||March||Store Kaleen||Australian Capital Territory||6810.24|
|11||2018||March||Store Chisholm||Australian Capital Territory||9853.25|
|11||2018||March||Store Waniassa||Australian Capital Territory||6933.52|
|11||2019||March||Store Alexander Heights||Western Australia||12550.61|
|11||2019||March||Store Paralowie||South Australia||9539.99|
|11||2019||March||Store Engadine||New South Wales||9223.82|
|11||2019||March||Store Ferntree Gully||Victoria||8188.41|
|11||2019||March||Store Fairfield (QLD)||Queensland||8618.32|
|11||2019||March||Store Dandenong Plaza||Victoria||15650.77|
|11||2019||March||Store Mount Barker (Cameron Street)||South Australia||15222.16|
|11||2019||March||Store Parramatta North||New South Wales||11626.07|
|11||2019||March||Store Wattle Grove||New South Wales||13026.99|
Solved! Go to Solution.
You are welcome. Thank you for your kind words.
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.
What is the problem? My answer is correct. See the screenshot below:
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?
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!
I am still confused. You can create a slicer for Week as well and select any one week.
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 .
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.
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.
That takes me to a sign-in page.
See if this measure works
Store(s) with highest sales = CONCATENATEX(TOPN(1,GENERATE(VALUES(Data[StoreName]),VALUES(Data[Week])),[Revenue]),Data[StoreName],", ")
Check out the News & Announcements to learn more.
Microsoft received the highest score of any vendor in both the strategy and current offering categories.
Mark your calendars and join us for our next Power BI Dev Camp!
Check out our new Discover Your Career Path blog post series and get all the details.