cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
iamtrangdoan Regular Visitor
Regular Visitor

Calculated column to find alternatives from a different table

Hi everyone,

 

I have the following datasets:

 

1. "Compare Data" table:

NameModelMaterial NoGroupCost
AW51005-01EEC100
AW51005-02EEC150
AW31005-03DIVIDER105
AW31005-04DIVIDER3000
AW51005-05EEC1215

 

2. "Full Data" table

Vendor NameEngine ModelMaterial NumberMaterial GroupRepair Cost
AW51005-01EEC80
BW51005-02EEC85
CW31005-03DIVIDER135
DW31005-04DIVIDER3005
EW51005-05EEC1115
FW21005-08EEC250
AW51005-09EEC350
AW51005-03EEC135
AW51005-25DIVIDER125
BW31005-2DIVIDER125.5
BW151005-3EEC135
RW51005-2EEC450

 

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


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

Proud to be a Datanaut!


5 REPLIES 5
Super User
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


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

Proud to be a Datanaut!


iamtrangdoan Regular Visitor
Regular Visitor

Re: Calculated column to find alternatives from a different table

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!

Super User
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.

 

image.png


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

Proud to be a Datanaut!


iamtrangdoan Regular Visitor
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],
        ", "
    )

 

 02.JPG

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!

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


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

Proud to be a Datanaut!