Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
@Dajopa
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 @Dajopa
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 ) )
)
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.
@Dajopa
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
21 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
45 | |
15 | |
12 |