Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
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 |
Solved! Go to Solution.
Hi @Powermac ,
You could try something like this, then filter based on Number of Offers or summarize the table.
Calculated column
Please accept as solution if this has answered the question- thanks!
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
)
)
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!! 👍😊
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
)
)
Hi @Powermac ,
You could try something like this, then filter based on Number of Offers or summarize the table.
Calculated column
Please accept as solution if this has answered the question- thanks!
User | Count |
---|---|
13 | |
8 | |
8 | |
7 | |
5 |
User | Count |
---|---|
21 | |
15 | |
15 | |
10 | |
7 |