Show top 10 of aggregated values using detail ds

[dainova]

Hi

I try to Show top 10  customers having 10+ claims, is it possible using just plain details dataset ?

Here is my data

Cust_Name | Member_ID | POS | Claim_ID | SERV_DATE | Issue
Aaron Ottix | 30776518 | ALAMO FL | 126557696 | 3/5/2019 | General Inquiry
Aaron Ottix | 30776518 | ALAMO FL | 126557697 | 3/5/2019 | General Inquiry
Aaron Ottix | 30776518 | ALAMO FL | 126566053 | 3/6/2019 | General Inquiry
Aaron Ottix | 30776518 | ALAMO FL | 126671735 | 3/5/2019 | Emergency Communication
Aaron Ottix | 30776518 | ALAMO FL | 126566054 | 3/5/2019 | General Inquiry
Aaron Spacemuseum | 323159330 | ALAMO FL | 123200353 | 7/20/2019 | General Inquiry
Aaron Spacemuseum | 323159330 | ALAMO FL | 123627548 | 10/11/2019 | General Inquiry

[v-jayw-msft]

Hi @dainova ,

Please check following steps as below:

1.Measure = DISTINCTCOUNT('Table'[ Claim_ID ])

2.Measure 2 =

   var rank_claim = RANKX(ALL('Table'[Cust_Name ]),[measure])

   return

   IF(rank_claim<=2,[measure])

Result would be shown as below:

If we change parameter:

Measure =

var rank_claim = RANKX(ALL('Table'[Cust_Name ]),[count])

return

IF(rank_claim<=1,[count])

Result would also change:

PBIX file as attached: 

https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/EddUYnJLNxREmpgceRU2U...

Hopefully works for you.

With Regards,

Jay

[dainova]

Tx all, Visual Filter worked for me.

Tx again for so massive help !!!

[natabird3 · Accepted solution]

You can use the visual filters and select to  filter based on top 10 claim ID count, no? 

[Anonymous]

Hi dainova - 

I didn't have Customer information in my dataset so I used Product as a proxy for Customer.

I solved this by creating a column in my sales transaction table called "Total Product Sales" with the code below.  Then it was a simple matter of creating a Top N filter on that column and searching for the Top 10.

Total Product Sales =

VAR _currentProduct = Sales[ProductKey]

VAR _productSales = FILTER(Sales, Sales[ProductKey] = _currentProduct)

VAR _return = COUNTROWS(_productSales)

RETURN _return

You should be able to simply replace Product with Customer to get it to work and then use a visual-level-filter to filter down to the Top 10.

Hope that helps. 

Eric

[Anonymous]

To clarify - 

"Total Product Sales" is actually the count of the rows in the sales transaction table that I have for that particular ProductKey (or in your case Cust_Num).  It's not the sum of the sales amount.  Probably should have labelled it better.

Eric