Show sales and no sales in same visual but only for available products

[bms2017]

Hi all,

I have a set of tables I want to build a visual from to show, for a customer, all available products to them, and any sales that have already happened. Here is the table structure (the formatting isn't helpful on here for tables but thought the data can be copied and pasted out easily to recreate the structure)

Customers table

ID	Area
C1	Germany
C2	Germany
C3	France
C4	Spain

Product availability table

Area	Product
Germany	A
Germany	B
Germany	C
France	A
France	B
France	C
Spain	A
Spain	B
Spain	C
Spain	D

Sales table

ID	Product	Quantity
C1	A	10
C1	B	20
C1	C	30
C2	A	100
C2	C	50
C4	B	25

The way i want the final visual to work is:

• choose a customer ID
• visual shows the products available only in their area
• alongside the products, visual displays any quantities that have been sold

So depending on the customer selected, the visual would work as follows:

• customer C1 would show products A, B and C being available, and also quantities sold for each
• customer C2 would show products A, B and C being available, but only quantities against A and C
• customer C3 would show products A, B and C being available, but no quantities for any
• customer C4 would show products A, B, C and D being available, but only quantities against B

Though the relationships between tables is intuitive, I can't figure out how to achieve the visual when combining with "Show items with no data" because (for example) doing so incorrectly shows customer C2 having product D being available.

I also want to introduce a "time available" dimension to the model want to understand how to do this generically that I can extend to other columns, if that's possible!

How can this be done?

Thanks for any help or advice!

[Eric_Zhang · Accepted solution]

@bms2017

I tried to add a area table and create relationship as below.

Then create a measure as below and then the visual show as expected.

Quantity measure = 
VAR val =
    CALCULATE (
        SUM ( Sales[Quantity] ),
        FILTER (
            Sales,
            Sales[Product] = LASTNONBLANK ( 'Product availability'[Product], "" )
        )
    )
RETURN
    IF (
        ISBLANK ( val )
            && NOT ( ISBLANK ( LASTNONBLANK ( 'Product availability'[Product], "" ) ) ),
        0,
        val
    )

See more details in the attached pbix file.

[bms2017]

Eric, thanks very much for your help on this - works perfectly, just what I wanted.