The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi - Currently my table (named Overview) is displaying Sites (via distinct site table) and Customers (via distinct customer table) as the Matrix Table's rows and the overall store sales + top 5 customer sales (depending on the time period picked).
I'm experiencing a problem where:
1. I can't seem to make a Top/Bottom 20 stores slicer (based off sales)
2. Adding Date columns to my Matrix Table rows removes my top 5 Customers row condition and instead shows 6 or more customer rows/exceeds query resources
To illustrate what i'm doing currently - I made the table's sales value by these measures:
Total Sales = SUM( Overview[Sales])
Then i used this measure as the Matrix Table value:
MEASURE-SALES = IF( ISINSCOPE( 'Unique Customers'[Customer] ), [Total Sales], CALCULATE( [Total Sales], REMOVEFILTERS( 'Unique Customers'[Customer] ) ) )
As mentioned before - i also made the table show each store's top 5 customers via this rank:
Customer Rank = VAR BaseTable = ADDCOLUMNS( CALCULATETABLE( SUMMARIZE( Overview, 'Unique Customers'[Customer], 'Unique Stores'[Store] ), REMOVEFILTERS( 'Unique Customers' ) ), "@val", [Total Sales] ) VAR Result = RANK( SKIP, BaseTable, ORDERBY( [@val], DESC ), PARTITIONBY( 'Unique Stores'[Store] ) ) RETURN Result
then i set this measure as = 1 in the table's filter
Customer is visible = VAR CustomerRank = [Customer Rank] VAR Result = IF( ( CustomerRank <= 5 && NOT ISBLANK( CustomerRank ) ) || NOT ISINSCOPE( 'Unique Customers'[Customer] ), 1 ) RETURN Result
My date table is below for reference (capped at current month for YOY measure purposes not mentioned above):
Date = ADDCOLUMNS ( CALENDAR ( DATE (2023, 1, 1), DATE (2025, 4, 30) ), "Year", YEAR([Date]), "Year-Month", FORMAT([Date], "yyyy-MM"), "Year-Month sort", EOMONTH([Date], 0) )
Would like if there was a test file sample on this if possible as i keep getting stuck/written measures i recieve return an error- the common problems i'm running into for 2. as mentioned before is:
- Query exceeds resource when Year-Month is added
- Rows exceed 5 customers when "Year" column is added / time range on slicer is picked
I noticed this question was open a few days so I decided to give it a try. I tried various approaches with DAX but I had the same issue with “ Query exceeds message… “.
For this reason I had a look via Power Query if this was possible and came up with the below. The general logic in Power Query for the attached file is :
Overview Table > Add a key : StoreCustomerKey
Overview Table > Add a key : StoreCustomerPeriodKey
Overview Table > Add a key : StorePeriodKey
Overview Table >Duplicate > Create Top 20 Overall Store Table > Merge to Main Table via Store
Overview Table >Duplicate > Create Bottom 20 Overall Store Table > Merge to Main Table via Store
Overview Table >Duplicate > Create Top 5 Customers Per Store > Merge to Main Table with StoreCustomerKey
Overview Table >Duplicate > Create Top 20 2023 Store Table > Merge to Main Table with StorePeriodKey
Overview Table >Duplicate > Create Bottom 20 2023 Overall Store Table > Merge to Main Table StorePeriodKey
Overview Table >Duplicate > Create Top 5 2023 Customers Per Store > Merge to Main Table with StoreCustomerPeriodKey
Overview Table >Duplicate > Create Top 20 2024 Store Table > Merge to Main Table with StorePeriodKey
Overview Table >Duplicate > Create Bottom 20 2024 Overall Store Table > Merge to Main Table StorePeriodKey
Overview Table >Duplicate > Create Top 5 2024 Customers Per Store > Merge to Main Table with StoreCustomerPeriodKey
Overview Table >Duplicate > Create Top 20 2025 Store Table > Merge to Main Table with StorePeriodKey
Overview Table >Duplicate > Create Bottom 20 2025 Overall Store Table > Merge to Main Table StorePeriodKey
Overview Table >Duplicate > Create Top 5 2025 Customers Per Store > Merge to Main Table with StoreCustomerPeriodKey
Limitations :
-The period can be filtered by Year and not by Month as Month filters would be resource heavy with this mode. Month is maybe possible ( maybe a python script in power query ) but I am not sure.
-Filters are a little bit too numerous but maybe you can clean that up with well placed slicers
Notes :
-Precise Power Query steps ( with extra custom columns, ranks, etc ) can be viewed if you download the file and connect to excel source.
-Linked only to your Overview table that you provided, but I think you can create relationships with your other tables where required
-Tested it and seems to work, but if you find something that is not correct please let me know . Will have another look.
-Below is a folder with the pbix file which you can link to you excel datasource which you provided in your question ( I worked off your datasource overview which you can link to yourt other tables ).
Top 20 Store Overall example :
Top 5 Customers per store
Example Store AS :
Top 5 Customer ( Overall ) Per store AS :
Top 5 Customer ( for year 2025 ) > Store AS :
Hope this helps
Antonio
User | Count |
---|---|
16 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
12 | |
8 | |
8 |