Conditional Column indicating Top N Values with filter

[Anonymous]

Hi all,

I am going crazy over the following issue...

I want to give items which are among the top 50 a certain value in a new column. Additionally I want to do this for different categories wherefore I want to include another filter. To achieve this I use the following formula:

TOP50_A = IF(RANKX(FILTER(Finance;RELATED(Groups[Category])="Category_A");CALCULATE(SUM(Finance[Sales]));;DESC;Skip)<=5040;2;0)

The result though in this column does not only states 2 in this column for category A products but also for all other if their sales is below the sales of the 5oth category A sales. What am I doing wrong here or is there a better approach for this?

Thanks in advance.

(The product category is not stated in the sales table but are connected via a 1-to-Many relationship - could this be an issue?)

[Anonymous]

Hi @v-frfei-msft 

Thanks for your response. I made up this this mokup for simplification:

I want the Top 2 sales rows to indicate a "2" for the category A, which includes Product 1 & 2 (indicated in green). But the result I get is that the filter only partly works and that the other products group also show a 2 (indicated with red)  if the sales result is above the lowest of the Top 2 of group A.

[v-frfei-msft]

Hi @Anonymous ,

To create measures as below.

Measure = var  k = FILTER('Product',RELATED(Groups[Category])="A")
var sales = CALCULATE(SUM(Finance[Sales]),KEEPFILTERS(k))
return
sales

top1 = IF([Measure]=BLANK(),BLANK(),MAXX(ALLEXCEPT('Product','Product'[Product]),[Measure]))

con = IF([Measure]=[top1] &&[Measure]<>BLANK(),2,BLANK())

col = IF([con]=2,"#533DFF",BLANK())

Then format the con by col as the picture below.

Pbix as attached.

Regards,

Frank

[v-frfei-msft]

Hi @Anonymous ,

Just update the measure as below.

Measure = CALCULATE(SUM(Finance[Sales]),KEEPFILTERS(k))

Regards,

Frank

[v-frfei-msft]

Hi @Anonymous ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank

[Anonymous]

Thanks for your help @v-frfei-msft , I just a had a chance to have a closer look at it.

It makes sense and points at the right direction, but is unfortunately not the final solution for me... I would need this in an conditional column since I would like to use it as a filter option respectively want to sum it up with other calculations like this, but cannot transform this measure into column it seems to me?

Also, I would need it not only for category A but also for category B etc. Is this possible in one measure/column?

Thanks again for your support here, appreciate it!

[v-frfei-msft]

Hi @Anonymous ,

Could you please share your sample data and excepted result to me if you don't have any Confidential Information.Please upload your files to One Drive and share the link here.

Regards,

Frank