Divide each value in a column by a value in that column that match the smallest index value

[youngmoneyduke]

Hi guys,

I have been really struggling with this simple looking task. Here what I want to do is create a ratio column that equal each value in the Equity column divided by the equity value that associate with the SMALLEST value in Index column.
In other word, I want to create a (Ratio) column that equal values in the Equity column divided by the first row value of the Equity column.

For example my data set right now: (note that M Equity and M Equity ratio are custom Measures)

My desire outcome should be:

Index	M Equity	M Equity Ratio
40	754665	1.0000
41	741742	0.9829
42	734340	0.9731
43	752745	0.9975
44	714297	0.9465
45	723576	0.9588

I should also note that this data table above can be filter by some date slicer, so the index value might change.
Thank you so much in advance.

[v-janeyg-msft · Accepted solution]

Hi, @youngmoneyduke 

According to your description, I think you can create a measure to calculate the desired result.

Like this:

M Equity Ratio =
VAR a =
    CALCULATE ( MIN ( 'Table 2'[Index] ), ALLSELECTED ( 'Table 2' ) )
VAR b =
    CALCULATE ( [M Equity], 'Table 2'[Index] = a )
RETURN
    DIVIDE ( [M Equity], b )

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[v-janeyg-msft · Accepted solution]

Hi, @youngmoneyduke 

According to your description, I think you can create a measure to calculate the desired result.

Like this:

M Equity Ratio =
VAR a =
    CALCULATE ( MIN ( 'Table 2'[Index] ), ALLSELECTED ( 'Table 2' ) )
VAR b =
    CALCULATE ( [M Equity], 'Table 2'[Index] = a )
RETURN
    DIVIDE ( [M Equity], b )

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[youngmoneyduke · Accepted solution]

Thank you so much Janey, your DAX code solve the problem that has been on my plate for a long time! Your code almost provide me the perfect answer. I would add another ALLSELECTED to var b in your formular for the complete answer as follow, which work perfect for me:

M Equity Ratio2 =

VAR a =

CALCULATE ( MIN ( 'HA SEPA FULL'[Index.1] ), ALLSELECTED ( 'HA SEPA FULL' ) )

VAR b =

CALCULATE ( [M Equity], 'HA SEPA FULL'[Index.1] = a, ALLSELECTED ( 'HA SEPA FULL' ) )

RETURN

DIVIDE ( [M Equity], b )

[parry2k]

@youngmoneyduke index is a measure or a column?

[youngmoneyduke]

Hi @parry2k

The index column is a column not a measure. It is originally not in my csv file, but I use Power Query to add that column to the table. 
Thanks in advance!