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

     

     

     

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