cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ekarakasli
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)

 

Thanks for your support

 

 

ASDAS.jpg

 

Adgdfdsız.jpg

1 ACCEPTED SOLUTION

Hi   @ekarakasli 

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:

 

222.png

Sorry again and hope this time would help.

 

Best Regards,

Kelly

View solution in original post

4 REPLIES 4
v-kelly-msft
Community Support
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:

111.png

 

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

Hi @v-kelly-msft

Thanks for your answer.

When i checked your answer i saw a problem.

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 ?

Adsız.jpg

 

Adsız2.jpg

 

 

Hi   @ekarakasli 

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:

 

222.png

Sorry again and hope this time would help.

 

Best Regards,

Kelly

View solution in original post

Hi @v-kelly-msft 

 

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

 

Best Regards

Enes

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors