Get a count of missing/void data

[tnelson]

I am trying to create a visualization of outlets that aren't selling particular products but the dataset that I am working doesn't count items not purchased. Is there a way to either count the number of missing items or a way to add in a static dataset that has the zeros and use that to count the missing items?

Any help is appreciated!

[v-lid-msft]

Hi @tnelson ,

You can create such a measure to count the blank or zero value in a table. If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

MissingCount =
COUNTROWS ( ALL ( 'Table' ) )
    - COUNTROWS (
        FILTER ( ALL ( 'Table' ), 'Table'[Value] <> 0 || 'Table'[Value] <> BLANK () )
    )

BTW, pbix as attached.

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more 

[tnelson]

Hi @v-lid-msft,

Thanks for the reply. That doesn't quite give me what I am looking for. Unfortunately, the data that I have is confidential but I can provide some sample data. 

    Customer        Product               Flavor       QTY
       1           Coca-Cola              Vanilla      13
       1            Pepsi                  Cherry       1
       1           Powerade             Fruit Punch     7
       13          Powerade             Fruit Punch     18
       13          Coca-Cola              Vanilla       12

So, this sample dataset shows that customer 1 has purchased 3 different products but customer 13 has only purchased 2. In this case, since customer 13 has not purchased any Pepsi, the dataset does not show Pepsi. Is there a way to count that Pepsi as missing so I can show it on a void report of customers that haven't purchased Products/flavors?

[v-lid-msft · Accepted solution]

Hi @tnelson ,

In your scenario, we can create calculate table using DAX to show the missing customer and product/Flavor.

MissProduct = 
EXCEPT (
    CROSSJOIN ( VALUES ( 'Table'[Customer] ), VALUES ( 'Table'[Product] ) ),
    SELECTCOLUMNS (
        'Table',
        "Customer", 'Table'[Customer],
        "Product", 'Table'[Product]
    )
)

MissFlavor = 
EXCEPT (
    CROSSJOIN ( VALUES ( 'Table'[Customer] ), VALUES ( 'Table'[Flavor] ) ),
    SELECTCOLUMNS (
        'Table',
        "Customer", 'Table'[Customer],
        "Product", 'Table'[Flavor]
    )
)

Then you can create the report or advanced measure based on these tables.

If it still doesn't meet your requirement, kindly share your excepted result to me using the fake sample data. Please upload your files to One Drive and share the link here.

BTW, pbix as attached.

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more 

[tnelson]

I copied and pasted that formula and put my own data in and received an error:

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

Any ideas on why I would receive that error and you don't?

[v-lid-msft]

Hi @tnelson ,

I guess that you create a measure using those formula? Actually I created a calculated table.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.