Divide by cell in preceding (above) row

[Sweet-T]

Hey community, 

I would like to calculate the conversion ratio for my Sales team. To do so, I need to divide a cell by that which preceeds it. 
Ex. 

Level        # of Opportunities             Conversion Ratio (need help with this)
  1                      66                                            -

  2                      45                                        45/66

  3                       9                                           9/45

  4                       6                                           6/9

All the opportunites are tracked and marked by level in a table 'BD Opportunities'.
Currently, I have created "counts" to determine how many opportunties there are at each level, ex:
Count Level 2 = CALCULATE(COUNTROWS(FILTER(ALL('BD Opportunity'), 'BD Opportunity'[Level] = "Level 2")))

Then, I have taken the ratio of two counts with: 
[Ratio - Level 2] = DIVIDE([Count Level 2], [Count Level 1]

This works, except for one problem. Due to the "ALL" function, in my Count Level X functions, I cannot slice the conversion ratio per the various Salespeople. 

The ALL function was included, because without it, the Count Level X funtion returns a 0 on any row that isn't Row X, rendering my Ratio function to have a 0 in the denominator.

Picture attached: For "Level 2" & "Level 3", I have removed the ALL function so you can see that the cells are 0 in each row other than Level 2 or 3,  Any help would be greatly apprecaited!

Cheers, 

T

[Greg_Deckler]

What you want is EARLIER. See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[Sweet-T]

Hey Greg, first off thanks for your response, I appreciate you taking the time to help provide a solution. 

I read through your MTBF post, as well as the Power BI Earlier function description, both are new to me. I've been trying to work through this, but cannot connect the dots here; doesn't this function still only apply to the current row? 

How can I apply this to pull the demoninator I need from the above row?

[Greg_Deckler]

So, this all depends on if that is a table or what you are displaying is a summary of a table, etc. But, assuming that it is an actual table in your database, you can do something like a calculated column like:

Column = [# of Opportunities] / MAXX(FILTER(ALL('BD Opportunity'), 'BD Opportunity'[Level] = EARLIER([Level])-1),[# of Opportunities])

So, basically it is very similar to what you were doing before by using ALL and then filtering it down. But, in this case you use EARLIER, which basically means "current value". So the current value in the 2nd row is 2 and you subtract 1 so you end up with the row before the current row for your denominator.

Once you learn how to use the "X" versions of DAX functions and get comfortable with table creation, the opportunities really open up with DAX. 

[Sweet-T]

Yeah good point, I am trying to do this on a visualization (summary of a table), so I believe I need to do this with a measure rather than a calculated column.. correct? 

Is there a way to use the 'EARLIER([Level])-1 in a measure? 

I can provide a more detailed Excel file for reference if that would be useful. 

[v-juanli-msft · Accepted solution]

Hi @Sweet-T

Both calculated columns and measures can help

calculated columns

Column =
CALCULATE (
    MAX ( [opportunities] ),
    FILTER ( ALL ( Sheet2 ), [level1] = EARLIER ( [level1] ) - 1 )
)

Column 2 = [opportunities]/[Column]

measure

Measure 2 =
CALCULATE (
    MAX ( [opportunities] ),
    FILTER ( ALL ( Sheet2 ), [level1] = SELECTEDVALUE ( Sheet2[level1] ) - 1 )
)

Measure 3 = MAX([opportunities])/[Measure 2]

Best regards

maggie

[Sweet-T]

Thanks Maggie! Looks like that will work. 

One caveat: I had to create a summarized table so that my [# of Opportunties] was an actual number. Previously (in the image I shared), the # of Opportunities column was just a Count of the "Level" column. 

This has introduced a new issue, my slicer no longer works on the data, since I created a new summarized table. 

Any thoughts on how to rectify this?