Forum Discussion

EmmaX's avatar
EmmaX
Frequent Visitor
8 years ago
Solved

count specific value in multiple columns

Hello everyone,

 

I'm very new to Power BI so please help me on this.

 

Say I have a table that looks something like this:

 

 

and I want to count the number of "yes" in each column, and come out with a table like this:

 

 

so that instead of having three individual bar charts that each shows counts of yes and no, I can make a chart which like this:

 

 

How can I achieve that in power bi?

 

Many thanks!

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Hi EmmaX

     

    You could create a calcuated table that summarizes your raw data

     

    Table = SUMMARIZECOLUMNS(
                    'Table1'[Brand],
                    "Comfort",CALCULATE(COUNTROWS('Table1'),'Table1'[comfort]="Yes"),
                    "Affordable Price",CALCULATE(COUNTROWS('Table1'),'Table1'[affordable price]="Yes"),
                    "Ease to Clean",CALCULATE(COUNTROWS('Table1'),'Table1'[ease to clean]="Yes")
                    )

     

    This produces the following output based on your sample data.

     

     

     

3 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    Hi EmmaX

     

    You could create a calcuated table that summarizes your raw data

     

    Table = SUMMARIZECOLUMNS(
                    'Table1'[Brand],
                    "Comfort",CALCULATE(COUNTROWS('Table1'),'Table1'[comfort]="Yes"),
                    "Affordable Price",CALCULATE(COUNTROWS('Table1'),'Table1'[affordable price]="Yes"),
                    "Ease to Clean",CALCULATE(COUNTROWS('Table1'),'Table1'[ease to clean]="Yes")
                    )

     

    This produces the following output based on your sample data.

     

     

     

    • EmmaX's avatar
      EmmaX
      Frequent Visitor
      8 years ago

      Wow, thanks for the quick reply.

       

      So it looks like in BI you have to manually code each column...which would be an issue when you have many columns...

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    although this format might be easier to get the barchart you need

     

    Table 2 = UNION(
             ROW(
                    "Axis" , "comfort" , 
                    "A" , CALCULATE(COUNTROWS('Table1'),'Table1'[Brand]="A" , 'Table1'[comfort]="Yes"),
                    "B" , CALCULATE(COUNTROWS('Table1'),'Table1'[Brand]="B" , 'Table1'[comfort]="Yes")
                ),
             ROW(
                    "Axis" , "Affordable price" , 
                    "A" , CALCULATE(COUNTROWS('Table1'),'Table1'[Brand]="A" , 'Table1'[affordable price]="Yes"),
                    "B" , CALCULATE(COUNTROWS('Table1'),'Table1'[Brand]="B" , 'Table1'[affordable price]="Yes")
                ),
             ROW(
                    "Axis" , "ease to clean" , 
                    "A" , CALCULATE(COUNTROWS('Table1'),'Table1'[Brand]="A" , 'Table1'[ease to clean]="Yes"),
                    "B" , CALCULATE(COUNTROWS('Table1'),'Table1'[Brand]="B" , 'Table1'[ease to clean]="Yes")
                )
)