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 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?)
Need Help

6 Replies

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

    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

  • 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