Excluding incomplete records in matrix

[Powermac]

Hello,

I have a data table that contains quotes from different vendors for a list of products. Besides the product ID there are also some additional product related columns that specify the product (size, color, area, etc.).

I just want to do a simple matrix and/or chart for comparing the sum of the different vendor quoations (assuming each prodcut shall be purchased one time) and do this also for different sizes, colors, area, etc.

So far it's the simpliest of all tasks for sure. My challenge is that not all vendors made a quote for each product and I only want to include those products in the sums where all vendors have made an offer.

Many thanks for you support! 🙂

In this example it looks ike Company B is th emost expensive but they made three quotations and the others only two. I would like to have only included the products where all vendors made offers (here P140 and P141).

Here is a snapshot of the data table:

ProductID	ProductType	Area	Color	Size	Vendor	Price
P140	X	A47	White	S5	Company B	183,64
P140	X	A47	White	S5	Company C	183,64
P140	X	A47	White	S5	Company A	157,63
P141	X	A47	White	S5	Company B	183,64
P141	X	A47	White	S5	Company C	183,64
P141	X	A47	White	S5	Company A	157,63
P142	Y	A47	Grey	S3	Company B	127,39
P142	Y	A47	Grey	S3	Company A	76,9
P144	Y	A47	White	S5	Company B	166,79

[djurecicK2 · Accepted solution]

Hi @Powermac ,

 You could try something like this, then filter based on Number of Offers or summarize the table.

Calculated column

Number of Offers = CALCULATE(DISTINCTCOUNTNOBLANK(Offers[Vendor]), FILTER(Offers,(EARLIER(Offers[ProductID])=Offers[ProductID])))

Please accept as solution if this has answered the question- thanks!

[Powermac]

Sorry for my delayed response. Many thanks for your support @djurecicK2 and @tamerj1 !

Both helped me a lot.

@tamerj1 : This really was 100% the solution that I was looking for. Many thanks for that!! 👍😊

[tamerj1 · Accepted solution]

Hi @Powermac 

Please refer to attached sample file with the solution

Common Products Amount = 
VAR NumberOfCompanies = 
    COUNTROWS ( ALLSELECTED ( 'Table'[Vendor] ) )
RETURN
    CALCULATE ( 
        SUM ( 'Table'[Price] ),
        FILTER ( 
            VALUES ( 'Table'[ProductID] ),
            CALCULATE ( 
                DISTINCTCOUNT ( 'Table'[Vendor] ), 
                ALLSELECTED ( 'Table'[Vendor] ) 
            ) 
                = NumberOfCompanies
        )
    )

[djurecicK2 · Accepted solution]

Hi @Powermac ,

 You could try something like this, then filter based on Number of Offers or summarize the table.

Calculated column

Number of Offers = CALCULATE(DISTINCTCOUNTNOBLANK(Offers[Vendor]), FILTER(Offers,(EARLIER(Offers[ProductID])=Offers[ProductID])))

Please accept as solution if this has answered the question- thanks!