Dashboard to compare Vendor to Global Average

[MarkusEng1998]

I want to recreate a Smartsheet dashboard in PowerBI where I can use a slicer for selecting the vendor. The dashboard will have bar graphs comparing the values of the global average (all of the vendors) to the selected vendor. Currently the source table has each vendor record as a row, including a row for the global average. Please advise how I need to restructure the data so that the global average will be one of the two rows in the dashboard.

 Thank you for any suggestions!

[MarkusEng1998]

Thank you very much, Liu! This is what I need.

[v-yangliu-msft · Accepted solution]

Hi  @MarkusEng1998 ,

Do you want to select some Vendor on the bar chart, one line shows this Vendor, and the other line shows Global Average for comparison?

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
DISTINCT('Table'[vendor])

2. Create measure.

Measure =
SUMX(FILTER(ALL('Table'),'Table'[vendor]="Global Average"),[Amount])

Flag =
var _select=SELECTCOLUMNS('Table 2',"1",[vendor])
return
IF(
    MAX('Table'[vendor]) in _select &&MAX('Table'[vendor])<>"Global Average",1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

If not your expected result, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly