How to measure the sum of X table (of this semester) / the sum of X table (of the last semester)

[parisaranda]

Hello and happy thanksgiving for you all,

I am having this issue with this tables and the formula should be:

sum(Matriculados) / sum(Matriculados (semester -1))

But I was not able to had a proper calculation, since I am here: 

 Now, I have the semester as the column name "Año" where it shows as an integer the year and "Semester" that shows 1 and 2. Also, the "Matriculados" column is an integer.

I would be glad to know that there is a way to calculate the required formula.

Thank you for your time. 

[Anonymous · Accepted solution]

Hi @parisaranda ,

I build a sample to have a test. Firstly add a calculated column : Rank by Year and Semester.

Rank by Year and Semester = RANKX('Table','Table'[Año]*10+'Table'[Semester],,ASC,Dense)

Measure:

Sum of X table (of this semester) / Sum of X table (of the last semester) = 
VAR _THIS_Semester =
    CALCULATE ( SUM ( 'Table'[Matriculados] ) )
VAR _LAST_Semester =
    CALCULATE (
        SUM ( 'Table'[Matriculados] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Rank by Year and Semester]
                = MAX ( 'Table'[Rank by Year and Semester] ) - 1
        )
    )
RETURN
    DIVIDE ( _THIS_Semester, _LAST_Semester )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

@parisaranda 

could you pls provide the sample data and expected output?