Calculated column to find alternatives from a different table

[Anonymous]

Hi everyone,

I have the following datasets:

1. "Compare Data" table:

Name	Model	Material No	Group	Cost
A	W5	1005-01	EEC	100
A	W5	1005-02	EEC	150
A	W3	1005-03	DIVIDER	105
A	W3	1005-04	DIVIDER	3000
A	W5	1005-05	EEC	1215

2. "Full Data" table

Vendor Name	Engine Model	Material Number	Material Group	Repair Cost
A	W5	1005-01	EEC	80
B	W5	1005-02	EEC	85
C	W3	1005-03	DIVIDER	135
D	W3	1005-04	DIVIDER	3005
E	W5	1005-05	EEC	1115
F	W2	1005-08	EEC	250
A	W5	1005-09	EEC	350
A	W5	1005-03	EEC	135
A	W5	1005-25	DIVIDER	125
B	W3	1005-2	DIVIDER	125.5
B	W15	1005-3	EEC	135
R	W5	1005-2	EEC	450

I'm looking to create a calculated column where I can later show and count who and how many alternative vendors to A and their Average Costs if "Model" and "Group" columns are used as slicers.

Ex:

If W5 is chosen from the "Model" slicer AND/OR EEC is selected from the "Group" slicer,

• Alternatives to A = B & E & R [Ignore A in the results]

• Number of Alternatives = 3

• Average Cost:

A = (100 + 150 + 105 +3000 +1215)/5 = 914

B = 85/1 = 85

E = 1115/1 = 1115

R = 450/1 = 450

I don't know if it should be a calculated column or a measure because I'd like to put these information in a Bubble Chart later where it shows A's Average Cost and its alternatives' costs with regard to "Model" and "Group" slicers.  

Please if you have any solutions, can you show me how to get this done? Thank you so much!

[Greg_Deckler · Accepted solution]

Here is a measure I created that probably doesn't do exactly what you want but hopefully gets you started. The measure returns the average of all vendors in the FullData table that are not the original vendor selected. You should be able to put this into a chart along with the Vendor Name as a the legend and it *should* work. 

Measure 2 = 
VAR __vendor = MAX('CompareData'[Name])
VAR __model = MAX('CompareData'[Model])
VAR __group = MAX('CompareData'[Group])
VAR __table = FILTER('FullData',[Vendor Name]<>__vendor && [Engine Model] = __model && [Material Group] = __group)
VAR __vendorAverage = AVERAGEX(FILTER(ALL(CompareData),[Name] = __vendor),[Cost])
VAR __othersAverage = AVERAGEX(__table,[Repair Cost])
RETURN
__othersAverage

See attached, Page 2

[Anonymous]

Hi @Greg_Deckler,

Thank you for your answer but could you please attach a screenshot of the Bubble Chart's visualization tab you created? My Power BI Desktop version at work isnot compatible with yours. I can't open your attached file.

Thanks so much!

[Greg_Deckler]

Well, I dont know what you are going to use as your x and y axis in a bubble cart, but you can see the measure at work in a simple Columbu chart more easily. Both are included.

[Anonymous]

Hi @Greg_Deckler, the graph works nicely, however, when I add more data, it doesn't show all alternatives . Do you have any idea why? And earlier someone helped me write this to show who are my alternative vendors, however, sometimes this measure which I use as a Card visual shows way too many results than what your chart produces. Any ideas?

Measure =
VAR mymodel =
    VALUES ( 'Compare Data'[Model] )
RETURN
    CONCATENATEX (
        EXCEPT (
            CALCULATETABLE (
                VALUES ( 'Full Data'[Vendor Name] ),
                'Full Data'[Engine Model] IN mymodel
            ),
            VALUES ( 'Compare Data'[Name] )
        ),
        [Vendor Name],
        ", "
    )

 

Note that in the above picture, when I chose HFCU & W5 simultaneously, there were 4 results in the chart whereas the Card visual showed 10. Any ideas?

Please find the link to my PBI:

https://liveconcordia-my.sharepoint.com/:u:/g/personal/t_oa_live_concordia_ca/EYMI-1UyKLZHnf3fs3rzQV...

In case you my version is not compatiable with your BI version, this is the link to my data source:

https://liveconcordia-my.sharepoint.com/:x:/g/personal/t_oa_live_concordia_ca/ERG7cYVW_6JFpnnKhxGKKQ...

Thanks for all your help so far!

[Greg_Deckler]

I don't see how that measure takes into account slicer selections if those slicers are driven off of a separate table rather than "Full Data".