Count and Rank by Specific Time Period when Filtering Certain Data

[kiwicam]

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!

[mahoneypat]

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]

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]

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 · Accepted solution]

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!

[amitchandak]

@kiwicam , Try like

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

[kiwicam]

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).