topic Re: Excluding incomplete records in matrix in DAX Commands and Tips

Excluding incomplete records in matrix

Powermac — Tue, 29 Nov 2022 17:46:33 GMT

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

Re: Excluding incomplete records in matrix

djurecic — Tue, 29 Nov 2022 19:47:36 GMT

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!

Re: Excluding incomplete records in matrix

tamerj1 — Wed, 30 Nov 2022 12:47:58 GMT

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 ) )

Re: Excluding incomplete records in matrix

Powermac — Fri, 02 Dec 2022 09:26:37 GMT

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

Both helped me a lot.

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