How to show elements that don't exist?

[ajmonster]

Here is my data model:

Store 1-* Sales

Item 1-* Sales

I want to show:

for each store, which items the store has not sold and the average sales for that item based on sales from other stores of that specific item.

Example:

Store A has sold $5 and $10 worth of Items 1 and 2, respectively.

Store B has sold $6 worth of Items 2 and 3 each.

Store C has sold Item 1 for $2.

I want to show a list that reads:

Store A

 Item 3..............................$6
Store B

 Item 1..............................$3.5
Store C

 Item 2..............................$8
 Item 3..............................$6

Ideally, this measure would also allow me to count the number of stores that are not selling a specific item.

Example: Using the above scenario, I would have a table that reads

Item 1..............................1

Item 2..............................1

Item 3..............................2

[v-deddai1-msft · Accepted solution]

Hi @ajmonster ,

We can try to create a separated calculated table and use following measure in table visuals to meet your requirement:

Table = 'Item'

Measure =

IF (

CALCULATE (

COUNTROWS ( Sales ),

FILTER ( Sales, Sales[Item ID] IN DISTINCT ( 'Table'[Item ID] ) )

) = 0,

CALCULATE (

AVERAGE ( 'Sales'[Price] ),

FILTER (

ALLSELECTED ( 'Sales' ),

'Sales'[Item ID] IN DISTINCT ( 'Table'[Item ID] )

)

),

BLANK ()

)



The measure is used to filter out the items not sold in the store and return its average price



Measure 2 =

SUMX (

    DISTINCT ( 'Item'[Item Name] ),

    CALCULATE (

        DISTINCTCOUNT ( 'Store'[Store ID] ),

        ALLSELECTED ( 'Sales'[Item ID] )

    )

        - CALCULATE ( DISTINCTCOUNT ( 'Sales'[Store ID] ) )

)



The measure is used to count the store that didn’t  sell the specific item.

Please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EYKEDu8HLuJAqRbVHK...

Best Regards,

Dedmon Dai

[ajmonster]

I've actually found a better solution:

Voids = 
EXCEPT(
    CROSSJOIN(
    //items that have been sold in the last 4 weeks
        SELECTCOLUMNS(
                SUMMARIZECOLUMNS(
                    'Item Key'[Product Name],
                    FILTER(
                        'Item Key',
                        'Item Key'[4 Week Average Sales] > 0
                    )
                ),
            "Product Name", 'Item Key'[Product Name]
        ),
    //stores that have sales in the last 4 weeks
        SELECTCOLUMNS(
            FILTER(
                'Store Key',
                'Store Key'[4 Week Store Sales] > 0
            ),
            "Store Number", 'Store Key'[Store Number]
        )
    ),
    //current 4 week sales excluding items with no sales last 4w but sales LY 
    SELECTCOLUMNS(
        FILTER(
            'Item Level',
            'Item Level'[$ Sales] > 0 && 'Item Level'[Time Frame] = "Latest 4 Weeks"
        ),
        "Product Name", RELATED('Item Key'[Product Name]),
        "Store Number", 'Item Level'[Store Number]
    )
)

This does exactly what I need. This is a DAX created table.

[amitchandak]

@ajmonster , Can you share better sample data and sample output.

[ajmonster]

What quality are you looking for from the sample input/output?

Item Table {Item ID}

{1, 2, 3, 4}
Store Table {Store ID}
{A, B, C, D}

Sales Table {(Store ID, Item ID, Sales Amount)}
{(A, 1, $5), (A, 2, $10), (B, 2, $6), (B, 3, $6), (C, 1, $2)}

I would like the output to be (using the table/matrix visual):

(A, 3, $6)
(B, 1, $3.5)
(C, 2, $8)

(C, 3, $6)

[v-deddai1-msft]

Hi @ajmonster ,

Have you ever try the pbix file I provided above? Did it  meet your requirement?

Best Regards,

Dedmon Dai