Clustered column chart - show percentage and values

[imahmood1]

Hi there,

I am completely new to Power BI and am trying new ways to show school data in graph form

I am trying to create a clustered column chart to show both our start of year and Autumn reading attainment by registration group by count and percentage. The values in the excel sheet are set as WW,WA,WT,B by child. I want to see the percentage/count(on the columns and not just by hovering over) of children that have achieved each of these values by registration group. At the moment it only shows count for both Start of year and Autumn 2 and the count is the exact same.

I hope this makes sense and any help is appreciated.

[v-yuezhe-msft]

@imahmood1,

Turn on Data Labels in the column chart, you will be able to see count values in the chart without hovering over. And if you want to display more measures except count in the chart, you can drag the field or measure to Tooltips of the chart, however, you will need to hover over the column chart to see the Tooltips field.


If you need  to see new dragged field without hovering over, consider to create Line and clustered column chart instead.

In addition, if you don't get expected count values, please share dummy data of your table and post desired result here.

Regards,
Lydia

[imahmood1]

Hi,

Thanks for your help.

I am now able to see the count of the children. What measure would i use to see the Percentage of the specific values e.g. WA, WW in that column? 

I am using this formula but it doesnt work..what am i doing wrong?

count values WA = CALCULATE(COUNTROWS('YEAR 1'),'YEAR 1'[Teacher Judgement - Rdg~2 Year 1 Baseline]=WA)

I have attached dummy data of what my data looks like.

[v-yuezhe-msft]

@imahmood1,

Could you please post expected percentage values based on the above sample data? Which logic do you use. (count of WA in cell D/ count of all values in cell D),  or (count of WA in cell D/count of all values in cell D) for specific Reg Group?

Regards,
Lydia

[imahmood1]

It would be the percentage/count per value in cell D and cell E per year group. so how many students are WA in the reg group 1A in cell D and then how many are WA in the reg group 1A in Cell E. ideally all this in one graph to show the comparison.

I hope this makes sense.

Iram

[v-yuezhe-msft]

@imahmood1，

I am not clear about  your expected result. Could you please post expected percentage number based on the above sample table ?

Regards,
Lydia

[imahmood1]

hi not sure what you mean...im looking for the below examples.

[v-yuezhe-msft · Accepted solution]

@imahmood1,

To calculate percentage of each value (WA,WW,WT,B) in cell D per reg group, please create the following measures. And you can  calculate percentage of each value (WA,WW,WT,B) in cell E per reg group by creating simiar measures.

Per-WW = (CALCULATE(COUNTA('Year 1'[Surname Forename]),FILTER('Year 1','Year 1'[Teacher Jud1]="WW"))/COUNTA('Year 1'[Surname Forename]))

per-WT = CALCULATE(COUNTA('Year 1'[Surname Forename]),FILTER('Year 1','Year 1'[Teacher Jud1]="WT"))/COUNTA('Year 1'[Surname Forename])

per-WA = CALCULATE(COUNTA('Year 1'[Surname Forename]),FILTER('Year 1','Year 1'[Teacher Jud1]="WA"))/COUNTA('Year 1'[Surname Forename])

per-B = CALCULATE(COUNTA('Year 1'[Surname Forename]),FILTER('Year 1','Year 1'[Teacher Jud1]="B"))/COUNTA('Year 1'[Surname Forename])



Regards,
Lydia

[imahmood1]

Hi, 

Thank you for your help, this has worked. I used the Stacked column chart which is what i think you had used? how am i able to see percentage ? at the moment i am only able to see the following numbers. Also this is all for one column e.g. cell D. Is there a way i could get these same measurements for Cell E on this same graph? E.G. if i used clustered column chart?

All help is appreciated.

[v-yuezhe-msft]

@imahmood1,

Click on your measures in your table, you can change its format to percentage under Modeling tab.


Regards,
Lydia