Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ajmonster
Helper II
Helper II

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

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

Hi @ajmonster ,

 

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

 

Table = 'Item'

 

Untitled picture6.png

 

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.

 

Untitled picture7.png

 

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

 

Best Regards,

Dedmon Dai

View solution in original post

5 REPLIES 5
v-deddai1-msft
Community Support
Community Support

Hi @ajmonster ,

 

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

 

Table = 'Item'

 

Untitled picture6.png

 

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.

 

Untitled picture7.png

 

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

 

Best Regards,

Dedmon Dai

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
Super User
Super User

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

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)

Hi @ajmonster ,

 

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

 

Best Regards,

Dedmon Dai

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.