Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
50 | |
40 | |
18 | |
14 | |
13 |
User | Count |
---|---|
99 | |
67 | |
28 | |
18 | |
13 |