CODEWITHSUNDEEP
powerbi
ggv
ggv

Reputation: 109

How to make exclusion table in Power BI

I am looking for a solution of this probably simple problem. I have two tables in Power BI: Inventory and Sales:

Inventory               Sales       
Item   Title            Item    Quantity
123    Soap             124     5
124    Detergent        123     8
125    Toothpaste
126    Tooth brush

How can I make a table that lists items not sold. I.e. I need to return:

Item   Title
125    Toothpaste
126    Tooth brush

Upvotes: 1

Views: 894

Answers (2)

Svendsen
Svendsen

Reputation: 57

You could create a calculated column: 

=IF(LOOKUPVALUE(Sales[item];Sales[item];Inventory[item]);BLANK();1)

and then filter for this column = 1

Or you could do this:

=IF(LOOKUPVALUE(Sales[item];Sales[item];Inventory[item]);BLANK();[title])

If you use this columns on your axis in a visual/table, only items not sold will show up.

Or you could use create a measure like this, assuming there is a relationship between the two tables on Item:

Products not being sold :=
IF (
  ISBLANK (
    CALCULATE (
      DISTINCTCOUNT ( Inventory[item] );
      FILTER ( Sales; Sales[item] = [item] )
    )
  );
  1;
  BLANK ()
)

if you add this measure to your visual/table only items not sold will show up

Upvotes: 0

Mike Honey
Mike Honey

Reputation: 15017

I would add a Measure to the Inventory table, e.g.

Item Quantity = 0 + SUM ( Sales[Quantity] )

Then I would add that Measure to the Visual level filters for your table visual, and set the filter to:

Show items when the value:
is
0

Upvotes: 1

Related Questions