cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
zmetzinger Frequent Visitor
Frequent Visitor

Building a Table that Calculates across rows and columns

Hi all,

 

I've built a table in excel that can calculate a target amount based on previous inputs, and I'd like to replicate it in BI (since it makes sharing easier and we already have a couple dashboard set up). Here is a screen shot of the table in excel: Untitled.png

 

 To calculate column I ("Target Confirmations"), I've added the Fill Pace Remaining (Column L) to the suceeding deadline's Fill Pace (Column G). The basic idea is that if we were supposed to get 5 applications at the first deadline, and we only received 4 applications, then our goal for the next deadline would be the second deadline's original goal +1 (to make up for that deficit). Therefore the "Target Confirmations" for November 16, 2018 is L3 + G4, January 25, 2019 is L4 + G5, and so on. 

 

The trouble I'm having is that in BI I don't know how to reference a specific value based on a preceeding row. I can subtract, multiply, etc values that are in the same row but different columns, but not in different columns AND rows. I've tried a couple different ways to get a value, but no matter what I try I always end up needing to reference somehow the preceeding deadline as it's values will impact the next deadline. 

 

Hoping I can get some support/clarification on how to build the report so I can reference preceeding rows, or on how I should be writing measures/calculated columns. 

 

Thanks in advance!

 

-Zach

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Building a Table that Calculates across rows and columns

Hi @zmetzinger,

 

You could create an index column in Query Editor and then create a calculated column with the formula below.

 

Column =
VAR a =
CALCULATE (
MAX ( 'Table1'[Fill Pace remaining] ),
FILTER ( 'Table1', 'Table1'[index] = EARLIER ( Table1[index] ) - 1 )
)
RETURN
a + 'Table1'[FillPace]

output.PNG

 

In addition, you could have a good look at this blog about using M query to achieve your desired output in Query Editor.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Building a Table that Calculates across rows and columns

Hi @zmetzinger,

 

You could create an index column in Query Editor and then create a calculated column with the formula below.

 

Column =
VAR a =
CALCULATE (
MAX ( 'Table1'[Fill Pace remaining] ),
FILTER ( 'Table1', 'Table1'[index] = EARLIER ( Table1[index] ) - 1 )
)
RETURN
a + 'Table1'[FillPace]

output.PNG

 

In addition, you could have a good look at this blog about using M query to achieve your desired output in Query Editor.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
zmetzinger Frequent Visitor
Frequent Visitor

Re: Building a Table that Calculates across rows and columns

Hey Cherry,

 

Thanks for the tip here! I tried incorporating the calculated column you suggested, but oddly I did not get the same results. Here's screen shot: 

Untitled.png

 Not sure why it's not calculating the rest of the column like in your example. 

 

Any help would be appreciated!

 

Thanks,

 

-Zach