Show Common Items both A & B have

[trdoan]

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

[LivioLanzo]

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

[trdoan]

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!

[LivioLanzo]

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

[trdoan]

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!

[LivioLanzo]

Hi @trdoan

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

[trdoan]

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.

[LivioLanzo]

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