topic Re: Count values above threshold in a matrix table in DAX Commands and Tips

Count values above threshold in a matrix table

Anonymous — Thu, 18 May 2023 13:26:26 GMT

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

Re: Count values above threshold in a matrix table

Anonymous — Thu, 18 May 2023 07:43:06 GMT

Unfortunately, I am not able to post the entire dataset.

Re: Count values above threshold in a matrix table

tamerj1 — Thu, 18 May 2023 08:37:15 GMT

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 ) ) )

Re: Count values above threshold in a matrix table

Anonymous — Thu, 18 May 2023 10:05:48 GMT

tamerj1, this is brilliant, it works! Thank you so much for the solution and swift reply. I will definitely accept it as solution. Just one improvement: the total comes as sum of each city counting. Is it possible to count the total figures independently?

Re: Count values above threshold in a matrix table

tamerj1 — Thu, 18 May 2023 13:58:56 GMT

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?

Re: Count values above threshold in a matrix table

Anonymous — Fri, 19 May 2023 07:41:11 GMT

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.

Re: Count values above threshold in a matrix table

tamerj1 — Fri, 19 May 2023 08:46:54 GMT

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 ) ) )

Re: Count values above threshold in a matrix table

Anonymous — Fri, 19 May 2023 09:36:52 GMT

This works like a charm! Fantastic! Thanks a million tamerj1.

Re: Count values above threshold in a matrix table

Anonymous — Tue, 06 Jun 2023 19:29:40 GMT

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.