cancel
Showing results for
Search instead for
Did you mean:
Member

## Show Common Items both A & B have

Hi everyone,

I have this table called "Data":

 Vendor Size Group Model Quantity Cost TAT Posting Date A S A150 150 450 67 July 7, 2018 A M A200 250 1500 75 June 22, 2018 A M A150 25 8500 85 July 9, 2018 C L A200 350 1250 125 March 5, 2018 C XL A500 150 6500 45 February 20, 2018 A M A900 385 475 40 January 29, 2018 A M A150 650 45 45 August 31, 2018 D M A150 65 7500 15 April 10, 2018 D M A300 140 3420 10 April 3, 2018 E S A150 20 10525 85 January 3, 2018 B S A150 30 10500 40 June 3, 2018 B S A150 450 450 64 April 3, 2018 E XS A900 45 75 60 January 3, 2018 F M A900 95 655 175 January 3, 2018 D XL A300 15 21500 25 January 3, 2018 D S A500 450 65 25 May 3, 2018 A M A350 250 450 22 January 3, 2018 B S A150 45 8500 28 January 3, 2018 A S A300 550 650 128 January 3, 2018 C M A150 1500 855 190 January 3, 2018 B M A150 65 1750 41 January 3, 2018 A L A500 75 1700 24 January 3, 2018 B S A900 55 9800 37 May 29, 2018 B M A500 150 850 83 April 18, 2018

How can I find common size groups that Vendor A & Vendor B share (ignoring all other vendors) and calculate the corresponding Average Cost for those common groups?

How would you go about this? Any help is very much appreicated! Thank you!!!

7 REPLIES 7
Super User

## Re: Show Common Items both A & B have

Hi @trdoan

try to build the below model and then add this measure:

```Avg Cost =
CALCULATE(
AVERAGE( Data[Cost] ),
CALCULATETABLE(
VALUES( Data[Size Group] ),
Vendors[Vendor] = "A"
),
CALCULATETABLE(
VALUES( Data[Size Group] ),
Vendors[Vendor] = "B"
)
)```

Did I answer your question correctly? Mark my answer as a solution!

Proud to be a Datanaut!

Member

## Re: Show Common Items both A & B have

Hi @LivioLanzo, thank you for helping me, however, I received this message even though I did the exact same thing as you had advised:

Error Message:

MdxScript(Model) (5, 14) Calculation error in measure 'Data'[Avg Cost]: The function AVERAGE cannot work with values of type String.

Do you have any ideas why?

Plus, it doesn't quite look like what I really want.

In the provided sample, the common Size Groups A & B both share are S & M. So, I was hoping to display those Size Groups as legend and Average Cost as value in a clustered column chart.

I'm not sure if your measure works out that way as it keeps giving me the same error message.

Thanks again!

Super User

## Re: Show Common Items both A & B have

Hi @trdoan

it looks like your 'Cost' column contains strings and not numbers, they column need to be a type number.

You can remove Vendor from the axis and just display the Sizes if that is what you wish to do, the formula stays the same

Did I answer your question correctly? Mark my answer as a solution!

Proud to be a Datanaut!

Member

## Re: Show Common Items both A & B have

Hi @LivioLanzo, your solution works great with my sample data that has a few models, however, it doesn't quite fit in with my real data having hundred of common models.

So, I'm thinking of having a Calculated Column that only shows common models between A & B and hides all uncommon items. Maybe with IF statement? Do you know how to go about this as I don't know what kind of syntax to use with IF that it can filter only common items.

Thanks a lot!

Super User

## Re: Show Common Items both A & B have

Hi @trdoan

why is it not working in the real data? is the real model different than what you posted?

Did I answer your question correctly? Mark my answer as a solution!

Proud to be a Datanaut!

Member

## Re: Show Common Items both A & B have

It does work @LivioLanzo. However, there are too many common models in my real data even though data structure is exactly he same. I'm just trying to approach my problem in a different way. That's why I asked if you knew how to do a Calculated Column to filter uncommon and common items.

Highlighted
Super User

## Re: Show Common Items both A & B have

Hi @trdoan

if you always want to compare A & B then we can also add a calculted column within the Size Groups dimension with a boolean True / False which identified if the size Group is shared between A and B. So then it will be easy to filter the model by this column.

Is it more or less what you are after?

thx

Did I answer your question correctly? Mark my answer as a solution!

Proud to be a Datanaut!