Forum Discussion

ajmonster's avatar
ajmonster
Icon for Helper II rankHelper II
6 years ago
Solved

How to show elements that don't exist?

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

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    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/EYKEDu8HLuJAqRbVHKbvHDYB18vmlRSuH-TmbRxQnyUxag?e=sFf73o

     

    Best Regards,

    Dedmon Dai

5 Replies

    • ajmonster's avatar
      ajmonster
      Icon for Helper II rankHelper II
      6 years ago

      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's avatar
        v-deddai1-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi ajmonster ,

         

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

         

        Best Regards,

        Dedmon Dai

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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/EYKEDu8HLuJAqRbVHKbvHDYB18vmlRSuH-TmbRxQnyUxag?e=sFf73o

     

    Best Regards,

    Dedmon Dai

    • ajmonster's avatar
      ajmonster
      Icon for Helper II rankHelper II
      6 years ago

      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.