How to create new measure in Powerpivot calculating index on running totals?

[JannikMM]

Hi,

First post so please bear with me if it doesn't live up to standards

i have a super annoying issue for something that should be really simple.

i got two tables joined in powerpivot - one with production actuals and the other with production plan. I have created a powerpivot table structured with week number (text format so YTD Dax formula will not solve the issue!) in rows and then production actuals and plan side by side in columns. The key figures are calculated as running totals in week (standard pivot table functionality). I now want to create a new measure that simply divides the running total per week for production actuals with running total per week for production plan. It should be super simple using e.g. the DIVIDE formula, but whatever I do it will not divide by the running total, but uses the actual value for the week.

any suggestions (preferably solved without VBA or SQL coding ?)

thanks

jannik

 

[scottsen]

I suspect reading this might help you a bit: Power Pivot Relationships | Power Pivot | Tiny Lizard

I don't typically see facts and actuals table directly related. They more often both relate to other lookups (calendar being a prime example)