Forum Discussion
Show benchmark stores in a table
Hi,
I have a store dashboard that consists of stores and store atributes in one table, and sales metrics in another.
I would like to create a dashboard where I select the store from a slicer, that then does two things:
- Compares store sales metrics to benchmark sales metrics ie. average across stores that have the same attributes
- Displays the benchmark stores in a table, ie. it ignores the store filter but still filters on the associated attributes
Help would be much appreciated. I tried looking for similar requests but couldn't find any.
Hi htipene ,
I have tried to formulate a solution.
Created a table called StoreSales, with the sales data.
Created a table called StoreSlicer, with attributes and the Store from this table is used as slicer.
These 2 tables are linked on Store.
I am not sure the the volume of records in your StoreSales table.
Using Power query
1. added the stores attributes 1 and 2 into Store Sales, merging on Store from both Store Sales and Store Slicer tables.
2. Grouped the table by Attribute1, Attribute 2,Year and added column AvgSales called Benchmark.
3. Removed unwanted columns
That is on the Store Sales table.
4. For the visualisation purpose created a copy of the Stores Slicer table and called StoreAttributeDisconnect.
The second table is a standalone table and not linked to anyother table.
5. Created a measure
ShowStore =
var _selAttribute1 = SELECTEDVALUE(StoreSlicer[Attribute 1 ])var _selAttribute2 = SELECTEDVALUE(StoreSlicer[Attribute 2 ])ReturnSUMX('StoreAttributeDisconnect', IF('StoreAttributeDisconnect'[Attribute 1 ] = _selAttribute1 &&'StoreAttributeDisconnect'[Attribute 2 ] = _selAttribute2 ,1,0)This is used to control the display of table with all stores with attributes as the selected store.
Here is the link to the pbix file
https://drive.google.com/file/d/1xxB5Y7Sl5s3op1jbr7A8zgR6T-MISBit/view?usp=sharing
Try it out.
Cheers
CheenuSing
Please accept this as a solution and give Kudos if it works for you.
5 Replies
- amitchandak
Super User
htipene Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
You might need measures like
sales =
calculate ( sum(Salesmetric[Value]), filter(Salesmetric, Salesmetric[Column] = "sales" ) )
Benchmark=
calculate ( sum(Salesmetric[Value]), filter(Salesmetric, Salesmetric[Column] = "Benchmark" ) )
Yes I can do that. So let's say I have a store and sales table as follows:
Store Attribute 1 Attribute 2 ABC Big store Regional DEF Big store Regional GHI Little store Metro JKL Little store Metro MNO Little store Regional PQR Big store Regional Store Year Sales ABC 1 100 ABC 2 200 DEF 1 150 DEF 2 250 GHI 1 100 When I select store ABC from the slicer, I want to have the below measures. The benchmark measures refer to the across all stores that are in the same (Big Store/Regional) benchmark group as ABC, (ABC and DEF):
Store Sales - Year 1 Sales - Year 2 Benchmark - Yr 1 Benchmark - Yr 2 ABC 100 200 125 225 And I want to have a table that displays the selected store plus associated benchmark stores + attributes:
Stores Attribute 1 Attribute 2 ABC Big store Regional DEF Big store Regional Hi htipene ,
I have tried to formulate a solution.
Created a table called StoreSales, with the sales data.
Created a table called StoreSlicer, with attributes and the Store from this table is used as slicer.
These 2 tables are linked on Store.
I am not sure the the volume of records in your StoreSales table.
Using Power query
1. added the stores attributes 1 and 2 into Store Sales, merging on Store from both Store Sales and Store Slicer tables.
2. Grouped the table by Attribute1, Attribute 2,Year and added column AvgSales called Benchmark.
3. Removed unwanted columns
That is on the Store Sales table.
4. For the visualisation purpose created a copy of the Stores Slicer table and called StoreAttributeDisconnect.
The second table is a standalone table and not linked to anyother table.
5. Created a measure
ShowStore =
var _selAttribute1 = SELECTEDVALUE(StoreSlicer[Attribute 1 ])var _selAttribute2 = SELECTEDVALUE(StoreSlicer[Attribute 2 ])ReturnSUMX('StoreAttributeDisconnect', IF('StoreAttributeDisconnect'[Attribute 1 ] = _selAttribute1 &&'StoreAttributeDisconnect'[Attribute 2 ] = _selAttribute2 ,1,0)This is used to control the display of table with all stores with attributes as the selected store.
Here is the link to the pbix file
https://drive.google.com/file/d/1xxB5Y7Sl5s3op1jbr7A8zgR6T-MISBit/view?usp=sharing
Try it out.
Cheers
CheenuSing
Please accept this as a solution and give Kudos if it works for you.
Thanks so much for this! Really great to see how it's done using Power Query.
Updated my original reply for better readability
