cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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.

1 ACCEPTED SOLUTION
Community Support

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

6 REPLIES 6

Thanks

Community Support

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

Community Support

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

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

Any solution on above query

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.