how to divide cells not column

[huva]

Hi There,

I need to calculate education transition rate and I have to divide students enrolled in 2014 by students enrolled in 2014. I cannot divide one ColumnC by ColumnB I need to divide cells. Below is an excel snapshot of what I want. I hope it makes is clear.

Thank you,

[Mariusz]

Hi @huva 

You can add a column to your table with the below DAX expression.

Column = 
VAR _prevYear = 'Table'[Year] - 1
VAR _prevG1 =
CALCULATE(
    SELECTEDVALUE( 'Table'[grade 1] ),
    ALL( 'Table' ),
    'Table'[Year] = _prevYear
)
RETURN DIVIDE( 'Table'[grade 2], _prevG1 )  

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[huva]

@Mariusz thanks, but it did not work. The created column was empty. 

[Mariusz]

Hi @huva 

Can you share your DAX expression?

The below is what I'm getting.

Column = 
VAR _prevYear = 'Table'[Year] - 1
VAR _prevG1 =
CALCULATE(
    SELECTEDVALUE( 'Table'[grade 1] ),
    ALL( 'Table' ),
    'Table'[Year] = _prevYear
)
RETURN DIVIDE( 'Table'[grade 2], _prevG1 )  

Best Regards,
Mariusz

Please feel free to connect with me.

[Mariusz]

Hi @huva 

Sorry, this is what I'm getting.

[huva]

@Mariusz 

Below is my DAX expression, my 'Table' is 'transition' and the grade 1 = 9_grade , grade 2 = 10_grade 

Column =

VAR _prevYear = 'transition'[Year] - 1

VAR _prevG1 =

CALCULATE(

SELECTEDVALUE( 'transition'[9_students] ),

ALL( 'transition' ),

'transition'[Year] = _prevYear

)

RETURN DIVIDE( 'transition'[10_students], _prevG1 )

thanks, 

Huva

[Mariusz]

Hi @huva 

Will there be duplicates years? If so it would blank the output as it is expecting only one value.

please see the below for ref

Best Regards,
Mariusz

Please feel free to connect with me.

[huva]

Yes, it is divided into Regions and Districts so there is duplication of years. Sorry, I am new to Power BI and thaught if DAX works for a smaller table will work for bigger as well. Also wanted to mention that my 'transition' table has more columns, could this be a reason that the DAX is not working? 

here the table by regions

thanks, 

Region	Year	9_students	10_students
Rigion1	2013	37666	21010
Rigion1	2014	39623	22434
Rigion1	2015	40747	24897
Rigion1	2016	37434	26867
Rigion1	2017	34101	22786
Rigion1	2018	36273	26248
Region2	2013	16054	10790
Region2	2014	16516	11079
Region2	2015	17174	11592
Region2	2016	16245	12082
Region2	2017	10045	7119
Region2	2018	15529	11526
Region3	2013	3901	3716
Region3	2014	3554	3558
Region3	2015	3301	3308
Region3	2016	3082	3091
Region3	2017	2967	2785
Region3	2018	2976	2751
Region4	2013	61955	47114
Region4	2014	64013	50229
Region4	2015	62802	51827
Region4	2016	58340	51512
Region4	2017	61898	48811
Region4	2018	59014	51823
Region5	2013	43311	34915
Region5	2014	43897	34360
Region5	2015	42639	36111
Region5	2016	42210	36588
Region5	2017	53752	43170
Region5	2018	42744	35940

[Mariusz · Accepted solution]

Hi @huva 

Please see the below, this should work.

Column = 
VAR _prevYear = 'transition'[Year] - 1
VAR _prevG1 =
CALCULATE(
    SELECTEDVALUE( 'transition'[9_students] ),
    ALLEXCEPT( 'transition', transition[Region] ),
    'transition'[Year] = _prevYear
)
RETURN DIVIDE( 'transition'[10_students], _prevG1 )  

The problem was that the formula was expecting one value in "SELECTEDVALUE" but was receiving multiple for each region in a year, now I've reintroduced a filter on the Region wit ALLEXCEPT, so we are returning a single value for previous year within each region. 

Hope this make sense.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[huva]

Hi @Mariusz 

It worked with the regional data, but when I used it in the regional and district level data with over 700 rows it did not work, but after amending it slightly it perfectly worked. I removed the SELECTEDVALUE and instead used the calculated measure. here is what the final version of the DAX:

Column = 
VAR _prevYear = 'transition'[Year] - 1
VAR _prevG1 =
CALCULATE(
    [nine grade enrolled],
    ALLEXCEPT( 'transition', transition[Region] ),
    'transition'[Year] = _prevYear
)
RETURN DIVIDE( 'transition'[10_students], _prevG1 )

So thank you very much @Mariusz for your help.

Regards,

huva  

[Mariusz]

Hi @huva 

Always happy to help!

Glad you worked it out.

Best Regards,
Mariusz

Please feel free to connect with me.