cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Calculated column to find alternatives from a different table

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Calculated column to find alternatives from a different table

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

Proud to be a Datanaut!

5 REPLIES 5
Super User

## Re: Calculated column to find alternatives from a different table

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

Proud to be a Datanaut!

Regular Visitor

## Re: Calculated column to find alternatives from a different table

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!

Super User

## Re: Calculated column to find alternatives from a different table

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.

Proud to be a Datanaut!

Regular Visitor

## Re: Calculated column to find alternatives from a different table

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?

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!

Super User

## Re: Calculated column to find alternatives from a different table

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