How to show aggregate value by filtering 2 country and show as total in bar including 2 country

[Aamir_khan]

Hi All

can we have feature where the graph can be selected. For e.g- when we select Australia and Canada, there's a bar that aggregate the 2 countriesonaly on the graph. And if I add France as well to the filter, the aggregate bar changes to average of 3.

How can we do that. I have attached the pbix file "Pbix Sample  ". can you please help me on that.

[Aamir_khan]

Thanks

[v-yangliu-msft · Accepted solution]

Hi  @Aamir_khan ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
var _Column={"Total"}
return
UNION(
    DISTINCT('Table'[Country]),_Column)

2. Create measure.

Measure =
var _select=SELECTCOLUMNS('Table',"1",[Country])
return
SUMX(ALLSELECTED('Table'),[Amount])

Value =
IF(
    MAX('Table 2'[Country]) ="Total",
    [Measure],
    SUMX(
        FILTER('Table',
        'Table'[Country]=MAX('Table 2'[Country])),[Amount])
    )

3. Result:

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

[v-yangliu-msft]

Hi  @Aamir_khan ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Measure =
var _select=SELECTCOLUMNS('Table',"1",[Country])
return
SUMX(ALLSELECTED('Table'),[Amount])

2. Result:

Does it meet your expected results

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

[Aamir_khan]

Hi Liu Yang

Thanks for helping me but I want separate dynamic bar for the Total based on filtre selection. Means when i select 2 country from filter then dynamic bar bar that aggregate the 2 countriesonaly on the graph

[Aamir_khan]

Any solution on above query 

[Aamir_khan]

Hi All

How can we do that. I have attached the pbix file "Pbix Sample  ". can you please help me on that on above query.