Forum Discussion

huva's avatar
huva
Frequent Visitor
7 years ago
Solved

how to divide cells not column

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,

Need Help
Show and Tell
  • Mariusz's avatar
    Mariusz
    7 years ago

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

     

     

10 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    7 years ago

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

     

    • huva's avatar
      huva
      Frequent Visitor
      7 years ago

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

      • Mariusz's avatar
        Mariusz
        Icon for Community Champion rankCommunity Champion
        7 years ago

        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 Repczynski