Create a measure (percentage) in Power BI by year

0
votes

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

2
powerbidaxpowerbi-desktopmeasurepowerbi-custom-visuals
Related: stackoverflow.com/questions/52393069 – Alexis Olson
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
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
@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

2 Answers

0
votes

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.

0
votes

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