Forum Discussion

kiwicam's avatar
kiwicam
Helper I
5 years ago
Solved

Count and Rank by Specific Time Period when Filtering Certain Data

Hi,   I am hoping someone can please help me solve the following issue. I am trying to count and rank the shops (Shops 1 to 😎 that sell a specific product (Product1) over a specific period (daily...
Need Help
  • kiwicam's avatar
    kiwicam
    5 years ago

    Ok, so this was solved by changing "ALLSELECTED" to "ALL".

     

    Before

    Shop Count = CALCULATE(DISTINCTCOUNTNOBLANK(sample_data[Shop]), ALLSELECTED(sample_data[Shop]))

     

    After

    Shop Count = CALCULATE(DISTINCTCOUNTNOBLANK(sample_data[Shop]), ALL(sample_data[Shop]))
     
     
    Thank you everyone for your help!
mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

Please try these expressions instead

 

Shop Count = CALCULATE(DISTINCTCOUNTNOBLANK(sample_data[Shop]), ALLSELECTED(sample_data[Shop]))

 

Shop Rank = RANKX(ALLNOBLANKROW(sample_data[Shop]), [Total Sales])

or

Shop Rank = var v1 = RANKX(ALL(sample_data[Shop]), [Total Sales])
return IF(ISBLANK([Total Sales]), BLANK(), v1)

 

Pat

 

kiwicam's avatar
kiwicam
Helper I
5 years ago

Hi Pat,

Thanks for the super quick response!!

Shop Count is perfect.

On shop rank the first suggestion produces a rank of 8 when there are only 7 shops.

I could work with this, but your second suggestion works a charm so I will use that.

Thank you again for your help!

  • kiwicam's avatar
    kiwicam
    Helper I
    5 years ago

    Hi Pat,

    Sorry, I spoke too soon.

    The shop count solution works when I don't apply the Shop Slicer.

    For example the shop count is 7 and 8 respectively in the image below.

    However, when I apply the shop slicer so the shop can only see their own volume, the shop count becomes 1 (see below).

    I need this to remain as 7 and 8 respectively.

    I don't suppose you have any further thoughts on how I can solve this?

    Thank you in advance!

     

    • kiwicam's avatar
      kiwicam
      Helper I
      5 years ago

      Ok, so this was solved by changing "ALLSELECTED" to "ALL".

       

      Before

      Shop Count = CALCULATE(DISTINCTCOUNTNOBLANK(sample_data[Shop]), ALLSELECTED(sample_data[Shop]))

       

      After

      Shop Count = CALCULATE(DISTINCTCOUNTNOBLANK(sample_data[Shop]), ALL(sample_data[Shop]))
       
       
      Thank you everyone for your help!