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

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'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!

    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support
      7 years ago

      Hi Anonymous ,

       

      Just update the measure as below.

       

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

      Regards,

      Frank