topic Re: Top N not working when ranking by percertage. in DAX Commands and Tips

Top N not working when ranking by percertage.

lfan5 — Tue, 14 Nov 2023 21:56:48 GMT

Hi all,

I am trying to summarize top 10 facility with [Total Claim Pay] >= 200 ranked by [Claim Pay %] in a desending order. This dax formular is not working. It won't summarize, and will give you only the first facility.

Top N Sum Total Claim Pay 200 =

CALCULATE(

[Total Claim Pay],

FILTER(

'Distinct Facility System',

[Total Claim Pay] >= 200

TOPN(

10,

'Distinct Facility System',

[Claim Pay %],

DESC

)

Any help will greatly appreicated!

Thanks,

Lin

Re: Top N not working when ranking by percertage.

FreemanZ — Wed, 15 Nov 2023 01:17:03 GMT

hi lfan5 ,

could you provide some sample data with expected result?

and how is [Claim Pay %] defined?

Re: Top N not working when ranking by percertage.

lfan5 — Wed, 15 Nov 2023 13:08:04 GMT

Hi FreemanZ

Here is the sample data.

Facility System	Total Claim Pay	Claim Pay %
A	201	100%
B	202	98%
C	203	50%
D	100	98%

The result would be 201+202 if TOPN 's choice is 2, they are facility systems with as least 200 claims. However, the above code would only display 201. I am wondering if it is because both [Total Claim Pay] and [Claim Pay %] are measures, maybe they need to be in a table.

Top N Sum Total Claim Pay 200 =

CALCULATE(

[Total Claim Pay],

FILTER(

'Distinct Facility System',

[Total Claim Pay] >= 200

TOPN(

'Distinct Facility System',

[Claim Pay %],

DESC

)

Re: Top N not working when ranking by percertage.

FreemanZ — Thu, 16 Nov 2023 02:00:13 GMT

hi lfan5 ,

try to provide the sample data directly from Distinct Facility System table.

Re: Top N not working when ranking by percertage.

Anonymous — Thu, 16 Nov 2023 06:47:11 GMT

Hi lfan5 ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Top N Sum Total Claim Pay 200 = var _table1= SUMMARIZE( FILTER(ALL('Distinct Facility System'),[Total Claim Pay]>=200), [Facility],"Total Claim Pay",[Total Claim Pay], "Claim Pay %",[Claim Pay %]) var _table2= ADDCOLUMNS(_table1,"index",RANKX(_table1,[Claim Pay %],,DESC)) return SUMX( FILTER(_table2,[index]<=2),[Total Claim Pay])

2. Result:

If it doesn't meet your expected results, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly