Creating a bar chart with value (from column 'Name') and group of values (from same column 'Name')

[anwarbi]

Hi,

I am trying to create a bar chart, where i want to compare Company (lets say company name - ABC) with its Peer group (this is any selction of companies from the slicer).

 I want to have two slicers one for the selection of company name and another slicer for the selection of group of companies, both slicers are from same column.

Is there a way that I can show both the company name and Peer group (based on the slicer selection) on the bar chart on X axis.

The result that i want to acheive is the below image, where the peer group is a selection of group of companies 

Thanks for your kind help.

   

[v-yiruan-msft · Accepted solution]

Hi @anwarbi ,

I created a sample pbix file(see the attachment), it include two methods in it. 

Method 1: Only create one slicer

Method 2: Create two dimension tables with [Name] field and two slicers

Company1 = VALUES('Table'[Name])

Company2 = VALUES('Table'[Name])

Measure1 = 
AVERAGEX (
    FILTER ( 'Table', 'Table'[Name] = SELECTEDVALUE ( 'Company1'[Name] ) ),
    'Table'[Rating]
)

Measure2 = 
AVERAGEX (
    FILTER ( 'Table', 'Table'[Name] = SELECTEDVALUE ( 'Company2'[Name] ) ),
    'Table'[Rating]
)

Create dimension tables

Best Regards

[anwarbi]

Hi,

I have got another query related to this.

When I am applying the below measure to calculate average, the measure is giving me correct results

Peer Average=

VAR SelectedPeers = ALLSELECTED('Peer group'[Company])

return

AVERAGEX(

    FILTER('Filtered data (4)','Filtered data (4)'[Company] IN SelectedPeers ),

    CALCULATE(AVERAGE('Filtered data (4)'[Score])))

But, whem I am applying the same measure but this time, instead of Averagex, I want to calculate the max of average for the category, so i am applying the below measure, but it is somehow giving me the max of all rows and not by average of category. Below is the measure to calculate Max of category.

Peer High =

VAR SelectedPeers = ALLSELECTED('Peer group'[Company])

return

MAXX(

    FILTER('Filtered data (4)','Filtered data (4)'[CCompany] IN SelectedPeers ),

    CALCULATE(AVERAGE('Filtered data (4)'[Score])))

 

Any idea, why it is working correctly for AVERAGEX but not for MAXX. 

Thanks

@Ahmedx 

[anwarbi]

Hi,

Thanks a lot!

The second method solves the problem, although I needed to use Allselected value instead of Selectedvalue for the filter.

Thanks.