Forum Discussion

kiwicam's avatar
kiwicam
Icon for Helper I rankHelper 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, weekly, monthly, etc.).

In addition, I have a limitation on what data I can share with each shop.

I can share the shop's own total sales, own rank, and the total count of shops, but I cannot share the total sales of other shops.

Initially I will be sharing the report via PDF, so I am using the slicer to filter by shop.

Please see the following sample.

I am using the following measures and initially the result looks fine.

  • Total Sales = SUM(sample_data[Sales])
  • Shop Count = CALCULATE(DISTINCTCOUNTNOBLANK(sample_data[Shop]), ALLEXCEPT(sample_data, sample_data[Product]))
  • Shop Rank = RANKX(ALL(sample_data[Shop]), [Total Sales])
However, some shops do not sell the product in some periods and they are being erroneously included in the count/rank for that period.
Please see the following example where on 11-Jan, only 7 shops sold the product.

 

How should I update my measures to get the correct result?

 

Thank you in advance for your help!

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!

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    kiwicam , Try like

    Shop Rank = RANKX(filter(ALLselected(sample_data[Shop],sample_data[Date]),sample_data[Date] =max(sample_data[Date])) , [Total Sales])

    • kiwicam's avatar
      kiwicam
      Icon for Helper I rankHelper I
      5 years ago

      Thanks amitchandak!

      I really appreciate the help and quick response!

      Unfortunately, your suggestion also ranks shops with no volume as "1"  (alongside the top ranked shop).

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft 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
      Icon for Helper I rankHelper 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
        Icon for Helper I rankHelper 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!