0

On picture you can see what I have, the number of students on each socioeconomic level (low, medium, high).

The database is conformed by students of 2017, 2019, 2020. And by year I have 1001 students in 2017, 1300 in 2019 and 1900 in 2020, a total of 4201 students.

I want to create a measure to graph the percentage of students by year. I mean, in 2017 that I have 193 students in level 'Bajo' (low in English), I want the percentage, 193/1001 *100, and the same for each year.

I've tried with quick measures of Power BI because I'm pretty new with DAX, but I think this needs to create a measure with code

Thanks in advance

enter image description here

jruizri
  • 34
  • 5
  • Related: https://stackoverflow.com/questions/52393069 – Alexis Olson Jun 11 '20 at 17:32
  • I tried the solution they gave there but it didn't work. This is what I tried: %Total = DIVIDE( SUM ('B1_vf'[students] ); CALCULATE( SUM( 'B1_vf'[students] ); ALLSELECTEd( 'B1_vf'[socioec_level] ) ) ) What I got its an 1, 1, 1 for each year. Students is a column of 1 – jruizri Jun 15 '20 at 16:11
  • That's the right approach. Do you have any other tables other than `B1_vf`? If you are using a dimension table, then you'd need to use `ALLSELECTED` on that table in your measure. – Alexis Olson Jun 15 '20 at 17:39
  • @AlexisOlson Yes I have other tables than B1_vf, it's a dimension table. The code above didn't work because socioeconomic levels type are TEXT, so I changed SUM to COUNTA but it's not working, it's giving me a percentage (of idk what) but not the one correct. Code: %Total1 = DIVIDE( COUNTA( B1_vf[socioec_level]); CALCULATE( COUNTA( B1_vf[year]); ALLSELECTED( B1_vf ) ) )*100 – jruizri Jun 15 '20 at 18:37

2 Answers2

0

I have created a table where -

  1. year has the values of 2017, 2020 etc
  2. BG has the value L(for low), M(for medium), H(for high)
  3. Name is a unique field for counting purpose

Measure = ROUND(DIVIDE(CALCULATE(DISTINCTCOUNTNOBLANK('Table'[Name]), 'Table'[BG] == "M", 'Table'[Year] == 2017),CALCULATE(DISTINCTCOUNTNOBLANK('Table'[Name]),'Table'[Year] == 2017),0) *100,2)

This measure gives percentage of students in M category for 2017. Using this measure for different values of years and BG you can achieve your desired output.

Rajat Anand
  • 21
  • 2
  • I tried this but I don't think it's going to work, because in 'values' (Graph) you are just able to put one measure. What I need it's the percentage of students in each level by year – jruizri Jun 15 '20 at 16:16
0

Thank you guys! I finally could do it. The code:

Percentage by socioeconomic level = DIVIDE(

CALCULATE(SUM(B1_vf[students]); ALLEXCEPT(B1_vf;B1_vf[year];B1_vf[socioeconomic_level])) ;

CALCULATE(SUM(B1_vf[students]); ALLEXCEPT(B1_vf;B1_vf[year]))   

)* 100

pd: B1_vf is the name of the table. :)

jruizri
  • 34
  • 5