Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
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!
Solved! Go to 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
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
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!
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.
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:
In case you my version is not compatiable with your BI version, this is the link to my data source:
Thanks for all your help so far!
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".
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |