Running Total

[Anonymous]

I'm trying to calculate a running total but I can't get it for some reason. Here's what my setup is:

So as you can see it's not adding the previous months value to the next. Here's what my measures involved look like:

Any ideas?

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, I create four tables to test:

         

Please take following steps:

1)Create four measures:

Annual Producer Sum = CALCULATE(SUM(User[Annual_Producer_Goal_c]))

       Sum of Quota % max per Month = MAXX(KEEPFILTERS(VALUES('Quota Percentage'[Month])),CALCULATE(SUM('Quota Percentage'[Quota %])))

       * Quoted Revenue = CALCULATE([Annual Producer Sum]*[Sum of Quota % max per Month])

       * Cumulative Quoted Revenue =

       VAR maxd =

           MAX ( 'Date'[Date] )

       RETURN

           SUMX (

               SUMMARIZE (

                   FILTER ( ALLSELECTED ( 'Opportunity' ), 'Opportunity'[Value] <= maxd ),

                   'Opportunity'[Month & Year],

                   "Revenue", [* Quoted Revenue]

               ),

               [Revenue]

           )

2)The result shows:

Here is my test pbix file:

pbix 

Best Regards,

Giotto Zhi

[d_gosbell]

So the filter in your cummulative measure looks wrong as it is comparing Opportunity[Value] to MAX(Date[Date])

The normal pattern would be:

* Cumulative Quote Revenue = CALCULATE([* Quoted Revenue], FILTER( ALL(Date[Date]), 'Date'[Date] <= MAX('Date'[Date]) ) )

But I don't think that will produce the correct result because it's effectively going to do SUM( Annual_Producer_Goal_c ) * MAX( [Quota %]) whereas I believe that you probably want to do SUM(  Annual_producer_Goal__c * [Quota &] )

The following expression will do the multiplication on a day by day basis then add the result.

* Cumulative Quote Revenue = SUMX( FILTER( ALL(Date[Date]), 'Date'[Date] <= MAX('Date'[Date]) ) ), [* Quoted Revenue] )

[amitchandak]

The formula seems correct unless there is issue with Opportunity value column.

If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...