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

Show Common Items both A & B have

Hi everyone,

 

I have this table called "Data":

 

VendorSize GroupModelQuantityCostTATPosting Date
ASA15015045067July 7, 2018
AMA200250150075June 22, 2018
AMA15025850085July 9, 2018
CLA2003501250125March 5, 2018
CXLA500150650045February 20, 2018
AMA90038547540January 29, 2018
AMA1506504545August 31, 2018
DMA15065750015April 10, 2018
DMA300140342010April 3, 2018
ESA150201052585January 3, 2018
BSA150301050040June 3, 2018
BSA15045045064April 3, 2018
EXSA900457560January 3, 2018
FMA90095655175January 3, 2018
DXLA300152150025January 3, 2018
DSA5004506525May 3, 2018
AMA35025045022January 3, 2018
BSA15045850028January 3, 2018
ASA300550650128January 3, 2018
CMA1501500855190January 3, 2018
BMA15065175041January 3, 2018
ALA50075170024January 3, 2018
BSA90055980037May 29, 2018
BMA50015085083April 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
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"
    )
)

 

2019-02-05_16-36-29.jpg2019-02-05_16-37-59.jpg

 


 


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


Proud to be a Datanaut!  

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

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

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