how to calculate unique stores selling products in powerpivot

Question

I'm not overly experienced with DAX, and my boss has asked me for some metrics which seem past my capability. Specifically, she wants to know in what percentage of the stores any item is being sold at.
We have a Stores table which is in a one to many relationship with the measures table relating by [STORE_ID]. The items table is also related 1-to-M to the measures table by [ITEM_ID]. the measures table contains which indicates among other things, the weekly [Sold] of an item.

My current logic has been to separate this problem into two more simple parts, (stores selling product)/ (total stores). finding the total stores which is an easy distinctcount in the stores table, the The next is more difficult. I tried Stores_Selling_Product = countrows(filter(filter(Measures, earlier(measures[ITEM_ID]) = measures[ITEM_ID]), EARLIER(Measures[STORE_ID]) <> Measures[STORE_ID])), but I found that only excluded the stores that matched the current store ID. Is there a way to exclude stores that have already been counted?

if it helps there is also a binary cell [SoldInStore] which is 1 if the item is sold in that store else 0. I'm pretty certain I'm going to have to use something other than countrows, but I don't know where to look. Any advice would super nice. Thanks in advance, -Mudkip.

score 1 · Answer 1 · answered Dec 25 '15 at 15:23

1

AllStores:=
COUNTROWS( ALL( 'Stores' ) )

StoresWithSales:=
COUNTROWS(
    CALCULATETABLE(
        'Stores'
        ,'Measures'
    )
)

%StoresSelling:=
DIVIDE( [StoresWithSales], [AllStores] )

The first measure will always return the total number of stores in the 'Stores' table, regardless of any filter context from the pivot table.

The second utilizes the magic of cross table filtering to filter 'Stores' to only those rows that have at least one corresponding row in 'Measures' based on whatever filter context currently exist on 'Measures'. Think of it as essentially the same as the following in SQL:

SELECT
    COUNT(1)
FROM Stores
WHERE EXISTS (SELECT 
                  COUNT(1)
              FROM Measures
              WHERE Measures.StoreKey = Stores.StoreKey
                AND <whatever filter context exists on Measures>
             );

answered Dec 25 '15 at 15:23

greggyb

3,728
1
11
32

Hey, thanks for the solution! Sorry for the late response, Christmas delayed me. This makes a lot of sense to me, but implementing it was a little funky. I found that when I implement this with no conditional in the filter part of calculatetable (calculatetable(store, Measures)) it returns every store in the store table as expected. But whenever I replace the general 'measures' with an actual filter such as Measures[Store with sales] = 1, it returns 1 every time. Any idea why this is? – Mudkip Dec 29 '15 at 19:47
I'm not quite sure I understand the question, but maybe this addresses what you're asking: When you manipulate relationships and context, you must do these separately, thus to apply a filter argument based on a field in Measures, you'd have to use a doubly nested CALCULATE(): CALCULATE( COUNTROWS( CALCULATETABLE( 'Stores', 'Measures' ) ), ) The inner CALCULATETABLE() adjusts the relationship navigation, the outer CALCULATE() adjusts the filter context on the table Measures being used in the relationship. – greggyb Dec 29 '15 at 20:12
Aha, Thanks for clarifying that. That extra calculate was what I needed. – Mudkip Dec 29 '15 at 20:29
Awesome. You'll see the same behavior if utilizing USERELATIONSHIP(). Relationship modification must be in a CALCULATE() internal to filter criteria. – greggyb Dec 29 '15 at 22:07

how to calculate unique stores selling products in powerpivot

1 Answers1