Forum Discussion

Seven's avatar
Seven
Regular Visitor
8 years ago
Solved

Group by with Filter

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's avatar
    Anonymous
    8 years ago

    HI Seven,

     

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

     

    Regards,

    Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    8 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    HI Seven,

     

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

     

    Regards,

    Xiaoxin Sheng

    • Seven's avatar
      Seven
      Regular Visitor
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        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

  • Seven's avatar
    Seven
    Regular Visitor
    8 years ago

    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.