Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
I appreciate if you can help me with the below. I have 4 cities and 4 stores and want to build a 4x4 performance matrix and count values above 90%. The counting should be both in columns and rows as shown in below snapshot. City 1 has only one store (#2) above 90%, while Store 1 has two cities above 90% (#2, #4). Important also that the matrix works with the four filters.
Both the attached dataset and matrix table are made up numbers and don't match. I posted just first 6 rows of the data, because the full dataset exceeds the 20K allowed characters. I will try to post again in a new thread coming soon. I hope it works, if not, please see if you can help with the below.
I googled and watched youtube videos and it seems I should use summarize, summarizecolumns, addcolumns, etc, but I havent been successful in my attempts for many days already.
Thank you for your time.
GEO | Store | Quarter | Year | Category | Target | Revenue |
City 1 | Store 1 | Q1 | 2022 | Actuals | 40.3 | 19.7 |
City 1 | Store 1 | Q1 | 2022 | Best Case | 40.3 | 19.7 |
City 1 | Store 1 | Q1 | 2022 | Commit | 40.3 | 19.7 |
City 1 | Store 1 | Q1 | 2023 | Actuals | 54.0 | 60.1 |
City 1 | Store 1 | Q1 | 2023 | Best Case | 54.0 | 60.1 |
City 1 | Store 1 | Q1 | 2023 | Commit | 54.0 | 60.1 |
Solved! Go to Solution.
@Anonymous
Please try
Above 90% =
VAR T1 =
SUMMARIZE ( 'Table', 'Table'[GEO], "@Percentage", [Percentage Measure] )
VAR T2 =
SUMMARIZE ( 'Table', 'Table'[Store], "@Percentage", [Percentage Measure] )
RETURN
SWITCH (
TRUE (),
HASONEVALUE ( 'Table'[GEO] ) && HASONEVALUE ( 'Table'[Store] ), [Percentage Measure],
HASONEVALUE ( 'Table'[Store] ), COUNTROWS ( FILTER ( T1, [@Percentage] > 0.9 ) ),
COUNTROWS ( FILTER ( T2, [@Percentage] > 0.9 ) )
)
Hi @Anonymous
Place the following measure in the values of the matrix instead of the current one.
Above 90% =
VAR T =
SUMMARIZE (
'Table',
'Table'[GEO],
'Table'[Store],
"@Percentage", [Percentage Measure]
)
RETURN
IF (
COUNTROWS ( T ) = 1,
[Percentage Measure],
COUNTROWS ( FILTER ( T, [@Percentage] > 0.9 ) )
)
@Anonymous
I'm not sure what exactly do you mean but I'm sure it can be done. So would you please clarify with a screenshot that includes some markups?
tamerj1, thank you for still being around.
Ideally, row "Total" should be 1 as only store 1 is above 90. The solution provided is generating 5 (the sum of above 90s in all cities). Hope that clarifies.
@Anonymous
Please try
Above 90% =
VAR T1 =
SUMMARIZE ( 'Table', 'Table'[GEO], "@Percentage", [Percentage Measure] )
VAR T2 =
SUMMARIZE ( 'Table', 'Table'[Store], "@Percentage", [Percentage Measure] )
RETURN
SWITCH (
TRUE (),
HASONEVALUE ( 'Table'[GEO] ) && HASONEVALUE ( 'Table'[Store] ), [Percentage Measure],
HASONEVALUE ( 'Table'[Store] ), COUNTROWS ( FILTER ( T1, [@Percentage] > 0.9 ) ),
COUNTROWS ( FILTER ( T2, [@Percentage] > 0.9 ) )
)
@tamerj1 , the formula works except that it's extremely slow to load. It takes around 1min to load/respond when a filter is changed (from Q1 to Q2, for instance). I was trying to find alternatives, but without success.
Can you please help once more? Thank you in advance.
Unfortunately, I am not able to post the entire dataset.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
22 | |
7 | |
6 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |