topic Re: Piechart diagram based on statistics (percentage calculation) in DAX Commands and Tips

Piechart diagram based on statistics (percentage calculation)

Katja262373 — Thu, 16 Nov 2023 18:05:42 GMT

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:

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

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

Re: Piechart diagram based on statistics (percentage calculation)

wdx223_Daniel — Fri, 17 Nov 2023 07:59:26 GMT

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)

Re: Piechart diagram based on statistics (percentage calculation)

Katja262373 — Fri, 17 Nov 2023 15:11:23 GMT

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

thank you A LOT for your help !!!

Re: Piechart diagram based on statistics (percentage calculation)

Katja262373 — Fri, 17 Nov 2023 12:00:37 GMT

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?

Re: Piechart diagram based on statistics (percentage calculation)

wdx223_Daniel — Sun, 19 Nov 2023 23:48:40 GMT

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

Re: Piechart diagram based on statistics (percentage calculation)

Katja262373 — Tue, 26 Dec 2023 22:52:39 GMT

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

Re: Piechart diagram based on statistics (percentage calculation)

wdx223_Daniel — Wed, 27 Dec 2023 04:05:58 GMT

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

Re: Piechart diagram based on statistics (percentage calculation)

Katja262373 — Sat, 30 Dec 2023 13:50:38 GMT

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