count specific value in multiple columns

[EmmaX]

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!

[Phil_Seamark]

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")
                )
)                 

[Phil_Seamark · Accepted solution]

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]

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...