Get max Value in Top N matrix

[Anonymous]

Hi all, I have created a matrix as follows, which displays a list of the top 25 employees by hrs. I've used the top n Filter to filter the data by hrs. What I would like to do is create a card from this list and display only the top value as per below it should be 159,2 on my dashboard. I've tried many different ways using the max function but its giving me strange results. The hours are summed per month by the matrix. Any help would be appreciated. Thanks

Company X	Department A	Employee number	July	August	Total
COMP A	DEPT A	1234	1280	31,2	159,2
		5678	79,2	51,4	130,7
		9101	72,8	51,5	124,3

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

Apologies but I am not permitted to share the information

[Ashish_Mathur · Accepted solution]

Hi,

This is then just hit and trial.  Try this measure

=MAXX(VALUES(Data[Employee number]),[SumHrs])

Hope this helps.

[Anonymous]

So simple thanks! This worked for me!

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Can you scramble data in the PBI file and then share the download link.

[AllisonKennedy]

@Anonymous
You have described the issue perfectly if it is what I think.

You said "The hours are summed per month by the matrix."
I don't know what strange results you're getting, but you need to create a measure that applies the same month row context that the measure provides when using the card visual. To do that, you could try SUMX(VALUES(DimDate[Month], [Total])

If that doesn't work, please provide more info on what your raw input data looks like as well as what the strange results are giving. Also if you are using a measure or column in the values of the matrix.

[Anonymous]

The measure I use for the values in the columns is 

SumHrs = sum(DATA[hrs])

The table contains values per day for each employee. E.g 

==========================

Date              | employee number | hrs | 

==========================

2020/07/01   | 1234                      | 500 |

2020/07/02   | 1234                      | 500 |

2020/07/12   | 1234                      | 280 |

2020/08/01   | 1234                      | 15   |

2020/08/02   | 1234                      | 15   |

2020/08/12   | 1234                      | 1,2  |

Departments and company are joined from other tables and a DIMDATE table is joined to get the month

Using SUMX(VALUES(DimDate[Month], SumHrs ) did not work

[AllisonKennedy]

@Anonymous
When you say it 'did not work' please can you explain what result or error you're getting and why it's wrong so we can shift gears into the right direction?
Cheers!

[amitchandak]

@Anonymous , Information is not sufficient. But you can use Rank and TOPN or Both one of them together

example

City and ID Rank = RANKX(all(Geography[City],Geography[City Id]),[Sales]) // Filter 25.  You can use only one column in all, I am giving an example of 2 because your matrix shows 2 column s

Top 25 City Rank = CALCULATE([Sales],TOPN(25,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id])) // 25 record are filtered

Different Example //Top2 is table Name

Top Rank = rankx(ALL(Top2),[PO],,DESC,Dense) + RAND()/1000 // Rank is created on measure PO + rand to aviod overlap
Top 25 Shop Rank = CALCULATE([PO],TOPN(25,all(Top2),[Top2 Rank],ASC),VALUES(Top2)) // Rank has been filtered , grouped on table

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415