Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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.
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") ) )
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.
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...
User | Count |
---|---|
107 | |
88 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |