Forum Discussion

Powermac's avatar
Powermac
Frequent Visitor
3 years ago
Solved

Excluding incomplete records in matrix

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:

ProductIDProductTypeAreaColorSizeVendorPrice
P140XA47WhiteS5Company B183,64
P140XA47WhiteS5Company C183,64
P140XA47WhiteS5Company A157,63
P141XA47WhiteS5Company B183,64
P141XA47WhiteS5Company C183,64
P141XA47WhiteS5Company A157,63
P142YA47GreyS3Company B127,39
P142YA47GreyS3Company A76,9
P144YA47WhiteS5Company B166,79

 

  • djurecic's avatar
    djurecic
    3 years ago

    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!

  • tamerj1's avatar
    tamerj1
    3 years ago

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

3 Replies

  • djurecic's avatar
    djurecic
    Icon for Super User rankSuper User
    3 years ago

    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!

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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
        )
    )
  • Powermac's avatar
    Powermac
    Frequent Visitor
    3 years ago

    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!! 👍😊