cancel
Showing results for
Did you mean: Frequent Visitor

## Last top N value of every column

Hi everyone,

I have 2 different date and calculate week numbers for these. (In second image)

I want to show last 3 values of every column. (In first image)  1 ACCEPTED SOLUTION  Community Support

Sorry for my mistake, the measure of “rank” should be corrected as below:

Measure =

var _rank=SUM('Table'[value])

var ran = RANKX(ALLSELECTED('Table'[Column]),CALCULATE(Max('Table'[Column])),,ASC,Dense)

var a = IF(_rank <>BLANK(),ran,BLANK())

return a

The function I marked bold should be modified to “Max”, not “Sum”.

And you will finally see as below: Sorry again and hope this time would help.

Best Regards,

Kelly

4 REPLIES 4  Community Support

Hi @ekarakasli

What you need is to create 2 measures:

1. set a rank according to the first column:

Measure =

var _rank=SUM('Table'[value])

var ran = RANKX(ALLSELECTED('Table'[Column]),CALCULATE(SUM('Table'[Column])),,ASC,Dense)

var a = IF(_rank <>BLANK(),ran,BLANK())

return a

2. get the last 3 values according to the rank.

Measure 2 =

var a =MAXX(ALLSELECTED('Table'[Column]),[Measure])

var b = a-2

return

IF([Measure] >=b && [Measure]<=a,SUM('Table'[value]),BLANK())

With the 2 measures, you will see as below: You can find the related .pbix file using the following URL:  https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/EZzHKKuBdSpEp5FrMY8lc...

Hope it would help.

Best Regards,

Kelly Frequent Visitor

This measure don't give same values every colum.

When i try your mesure on my date, I have some situation again.

Do you have another alternative or solution for this ?    Community Support

Sorry for my mistake, the measure of “rank” should be corrected as below:

Measure =

var _rank=SUM('Table'[value])

var ran = RANKX(ALLSELECTED('Table'[Column]),CALCULATE(Max('Table'[Column])),,ASC,Dense)

var a = IF(_rank <>BLANK(),ran,BLANK())

return a

The function I marked bold should be modified to “Max”, not “Sum”.

And you will finally see as below: Sorry again and hope this time would help.

Best Regards,

Kelly Frequent Visitor

It's working. Thank you so much for your support.

Best Regards

Enes    