Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Katja262373
Helper I
Helper I

Piechart diagram based on statistics (percentage calculation)

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

IMG_0081.jpeg

Best regards,

Katia 

7 REPLIES 7
wdx223_Daniel
Super User
Super User

create a dimension table like this

wdx223_Daniel_0-1700207770720.png

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)

 

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

thank you A LOT for your help !!!

IMG_0131.jpeg

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?

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

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

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.