Group by with Filter

[Seven]

Hello,

I have the Data like this:

                  Hobbies          Country

Mark          Swimming       Germany

David         Football           Italy

Maria         Swimming       Germany

Peter          Biking              Italy

Now I would like to create a column chart in Power by where I can set the Filter "Country"

So in the chart the "Top"-favourite Hobbies should be displayed with a Count of the Hobby. With a slicer, I would like to choose the country "ALL","Germany","Italy"

The result should be:

Chart for All countries:

Swimming: 2

Football: 1

Biking: 1

Chart for Germany:

Swimming: 2

Chart for Italy:

Football: 1

Biking: 1

I hope you can understand and help me.

Thank you very much.

[Anonymous · Accepted solution]

HI @Seven,

You can refer to below graph to know how to create a visualization to achieve your requirement.

Regards,

Xiaoxin Sheng

[Seven]

Hello,

I have the Data like this:

                  Hobbies          Country

Mark          Swimming       Germany

David         Football           Italy

Maria         Swimming       Germany

Peter          Biking              Italy

Now I would like to create a column chart in Power by where I can set the Filter "Country"

So in the chart the "Top"-favourite Hobbies should be displayed with a Count of the Hobby. With a slicer, I would like to choose the country "ALL","Germany","Italy"

The result should be:

Chart for All countries:

Swimming: 2

Football: 1

Biking: 1

Chart for Germany:

Swimming: 2

Chart for Italy:

Football: 1

Biking: 1

I hope you can understand and help me.

Thank you.

[Anonymous · Accepted solution]

HI @Seven,

You can refer to below graph to know how to create a visualization to achieve your requirement.

Regards,

Xiaoxin Sheng

[Seven]

Hi Xiaoxin Sheng,

thank you very much. This is helping a lot.

Is it now possible to set a constant line, which is different for every filter.

The line should bei the total count of people of every country.

e.g. 2 german people are swimming. In the data there 4 germans. So it should be made clear (with the line at 4), that half of the Germans are swimming.

Regards,

Sven

[Anonymous · Accepted solution]

Hi @Seven,

You can switch visual type to line and clustered column chart and use below measure as line value:

Analytic Line = 
DIVIDE (
    CALCULATE (
        COUNT ( Table1[Name] ),
        VALUES ( Table1[Hobbies] ),
        VALUES ( Table1[Country] )
    ),
    CALCULATE ( COUNT ( Table1[Name] ), ALLEXCEPT ( Table1, Table1[Country] ) ),
    -1
)

Regards,

Xiaoxin Sheng