Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Conditional Column indicating Top N Values with filter

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 w...
Need Help
v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

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's avatar
v-frfei-msft
Community Support
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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!