Piechart diagram based on statistics (percentage calculation)

[Katja262373]

Dear all,

I’m a Data Analysis course student, trying to make a Piechart diagram to represent a sample’s educational background (60.000 people in the sample). There are 5 groups of educational institutions (column [edulvlb] of my table ‘education’: basic education (codes 000,113,129), school diploma holders (codes 212 || 213 || 221 || 222 || 223 || 229 || 311 || 312 || 313 || 321 || 322 || 323), college diploma holders (codes 412 || 413 || 421 || 422 || 423 || 510 || 520), university diploma holders (codes 610 || 620 || 710 || 720) and PhD degree holders (code 800).

So I need to count the codes per category (for example, how many times 000,113, 129 appear in column [edulvlb]) and then divide it by total amount of unfiltered rows (about 60.000 rows) of my entire datascheet. Therefore the choice of || which means OR in data selection…

So I have divided this process in 2 steps:

1.

Basis = CALCULATE (COUNTROWS(education),FILTER ('education',education[edulvlb] = 000 || 113 ||129))

School = CALCULATE (COUNTROWS(education),FILTER ('education',education[edulvlb] = 212 || 213 || 221 || 222 || 223 || 229 || 311 || 312 || 313 || 321 || 322 || 323))

College = CALCULATE (COUNTROWS(education),FILTER ('education',education[edulvlb] = 412 || 413 || 421 || 422 || 423 || 510 || 520))

Uni = CALCULATE (COUNTROWS(education),FILTER ('education',education[edulvlb] = 610 || 620 || 710 || 720))

PhD = CALCULATE (COUNTROWS(education),FILTER ('education',education[edulvlb] = 800))

2.

PercentageBasis = DIVIDE([Basis], Calculate([Basis], ALL('education')))

PercentageSchool = DIVIDE([School], Calculate([School], ALL('education')))

PercentageCollege = DIVIDE([College], Calculate([College], ALL('education')))

PercentageUni = DIVIDE([Uni], Calculate([Uni], ALL('education')))

PercentagePhD = DIVIDE([PhD], Calculate([PhD], ALL('education'))

However, the piechart shows 5 segments of totally similar size 20%.. what have I done wrong here..maybe you could advise ?? Looking forward to hearing from you, thank you again for your help !!

 

Best regards,

Katia 

[wdx223_Daniel]

create a dimension table like this

and link the code column to the code column of 'education, one to many

put the Groups column in the Legend area

Create a measure as below, and put it in the Values area

CodeCount=COUNTROWS('education)

[Katja262373]

Dear Daniel, I've checked the Cardinality "many to one" and it worked!!!!

thank you A LOT for your help !!!

[Katja262373]

Hi Daniel, I forgot to say that I actually have a column with unique values (every respondent of a survey has an id-number - is mentioned in IDNO column)... maybe I should include it in the graph?

[wdx223_Daniel]

if the column values are unique, DISTINCTCOUNT(education[IDNO]) will give a same result with COUNTROWS(education)

[Katja262373]

Thank you very much for your kind help Daniel! Merry Christmas! 
p.s. sorry for my belated response, I gave birth to a baby girl .....

[wdx223_Daniel]

Congratulations，I wish the baby grow up healthy and happy.

[Katja262373]

Thank you dear Daniel!! Happy new 2024 year!!!!!