cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sweet-T Member
Member

Divide by cell in preceding (above) row

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

Power BI Help pic.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Divide by cell in preceding (above) row

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]

4.png

measure

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

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

5.png

 

 

Best regards

maggie

 

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Divide by cell in preceding (above) row

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Sweet-T Member
Member

Re: Divide by cell in preceding (above) row

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?


Super User
Super User

Re: Divide by cell in preceding (above) row

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. 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Sweet-T Member
Member

Re: Divide by cell in preceding (above) row

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. 

Community Support Team
Community Support Team

Re: Divide by cell in preceding (above) row

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]

4.png

measure

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

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

5.png

 

 

Best regards

maggie

 

View solution in original post

Sweet-T Member
Member

Re: Divide by cell in preceding (above) row

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? 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 105 members 1,108 guests
Please welcome our newest community members: