Cumulative from average x number of contracts

[JakeWestonD3]

Aloha!

I'm having an issue with getting correct Cumulative totals. Basically, I need to take the final result from a series of measures and show its running (cumulative) totals. But what i'm actually getting is the cumulative totals of each individual measure in the series and then these are being multipled into a result.

Example and steps:

Step 1: calculate the average value of an award.

Average Value of Awards ($) =

CALCULATE(

             DIVIDE(

                      SUM(Table 1[Award Value($)]),
                      SUM(Table 1[Amount of Awards(#)])))

Date:	Sum of Award Value ($):	Amount of Awards (#):	Average Value of Awards ($):
1/2/2021	$579,809.11	32	$18,119.03
1/9/2021	$163,580.44	12	$13,631.70
1/16/2021	$378,594.81	16	$23,662.18
1/23/2021	$144,281.36	14	$10,305.81

Step 2: Average Value of Awards x New Proposals (Propsals can turn into awards):

Value of New Proposals ($) =
CALCULATE(
                     [Average Value of Awards ($)] * SUM(Table 1[New Proposals]))

Date:	Average Value of Awards ($):	Amount of New Proposals:	Value of New Proposals ($):
1/2/2021	$18,119.03	17	308023.5897
1/9/2021	$13,631.70	19	259002.3633
1/16/2021	$23,662.18	20	473243.5125
1/23/2021	$10,305.81	17	175198.7943

Step 3: Cumulative Value of New Proposals:

Cumulative Value of New Proposals ($) = 

CALCULATE([Value of New Proposals ($)],

                          FILTER(ALLSELECTED('Calendar'),

                                        'Calendar'[Date] <= MAX('Calendar'[Date]) &&

                                         'Calendar'[Fiscal_Year] = MAX('Calendar'[Fiscal_Year])))

Date:	Value of New Proposals ($):	Cumulative Value of New Proposals ($):	Expected Cumulative ($):
1/2/2021	308023.5897	308023.5897	308023.5897
1/9/2021	259002.3633	608227.8136	567025.953
1/16/2021	473243.5125	1047185.403	1040269.466
1/23/2021	175198.7943	1249154.021	1215468.26

The calculation that produces the wrong cumulative:
Cumulative of Average Award Values * Cumulative of New Proposals

Cumulative of Average Award Values =
CALCULATE([Average Value of Awards ($)],

                              FILTER(ALLSELECTED('Calendar'),

                              'Calendar'[Date] <= MAX('Calendar'[Date]) &&

                               'Calendar'[Fiscal_Year] = MAX('Calendar'[Fiscal_Year])))

Cumulative of New Proposals =
CALCULATE(SUM(Table 1 [New Proposals],

                              FILTER(ALLSELECTED('Calendar'),

                              'Calendar'[Date] <= MAX('Calendar'[Date]) &&

                               'Calendar'[Fiscal_Year] = MAX('Calendar'[Fiscal_Year])))

Date:	Cumulative of Average Award Value	Cumulative of New Proposals	Cumulative Value of New Proposals ($):
1/2/2021	18119.03	17	308023.5897
1/9/2021	16895.22	36	608227.8136
1/16/2021	18699.74	56	1047185.403
1/23/2021	17111.7	73	1249154.021

If you multiply those values together it produces the "Cumulative Value of New Proposals ($)". I'm not sure why this happens but I feel like i'm making a very simple mistake with my intials calculations. Can someone please advise on how to fix this so i can get the "Expected Cumulative ($)"? Also, please explain why this is happening so i can avoid making the mistake again in future.

Mahalo!

[v-kkf-msft]

Hi @JakeWestonD3 ,

Try the following formula:

Cumulative Value = 
SUMX (
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
            && 'Calendar'[W_Fiscal_Year] = MAX ( 'Calendar'[W_Fiscal_Year] )
    ),
    [Step 2 - Value of New Proposals ($)]
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Clearly show the problem there.

[JakeWestonD3]

https://drive.google.com/file/d/1B8Lqu-ztVJAMoIF3pTi77GFB_itaK6QT/view?usp=sharing

[Ashish_Mathur]

Hi,

I still do not understand what you want?  You have shared 3 super miniature visuals on a page without any explanation.  May be someone else will help you. 

[lbendlin]

In situations like this you don't really want to use nested measures. Rather make sure you have a very crisp definition of the required calculation and then create a measure just for that calculation. That way you control the context transitions, not the other measures. 

[JakeWestonD3]

Hey Ibendlin,

Could you provide an example of what you mean?

It sounds like you're suggesting to perform each calculation in the same measure (perhaps as variables)?

I tried:

Cumulative Value of New Proposals ($) =

CALCULATE(

                DIVIDE(

                         SUM(Table 1[Value of Awards $]),

                         SUM(Table 1[Total Awards]))*

                         SUM(Table 1[New Proposals]),

FILTER(
           ALL('Calendar'),

                 'Calendar'[Date] <= MAX('Calendar'[Date]) &&

                 'Calendar'[Fiscal_Year] = MAX('Calendar'[Fiscal_Year])))

This made no change to the cumulative outcome.

[lbendlin]

Cumulative Value of New Proposals ($) =
var md = MAX('Calendar'[Date])
var my = MAX('Calendar'[Fiscal_Year])
Return CALCULATE(
                DIVIDE(SUM(Table 1[Value of Awards $]),SUM(Table 1[Total Awards]),0)
                * SUM(Table 1[New Proposals]),
                 'Calendar'[Date] <= md,
                 'Calendar'[Fiscal_Year] = my
)

This is just an illustration.  No idea if your calculation matches your intent - it's not clear from your description of the issue (at least not to me). To me it feels that you want to use SUMX or PRODUCTX instead.

[JakeWestonD3]

Hey Ibendlin,

I tried the method you suggested but it returned the same results. I think the easiest way to understand is to write out the formula and explain the goal of the calculation.

Every week I have a recorded dollar value of awards (Contracts) and a total count of awards. The first calculation I do is the average dollar value per award

Value of Awards ($)/Count of awards = Average Value of Awards ($)

Also, every week, I have a count of new proposals. New Proposals typically become awards in a couple of months. There is no $ value associated with these proposals because they haven’t been awarded yet. But I would like the approximate the $ Value that will come from them in a few months. So, I simply multiply the above "Average Value of Award ($)" by the count of proposals.

Average Value of Awards ($) X Count of New Proposals = Approximate Future Value of Awards ($)

I want the Cumulative (Running Total) of these values. But my cumulative results are incorrect. It appears that the cumulative measure is being applied independently to the individual measures of the above calculation and then they are being multiplied by each other.

Cumulative(Average Value of Awards ($))
X
Cumulative(Count of New Proposals)
=
Incorrect Cumulative

I want (Row by Row):

Cumulative (Approximate Future Value of Awards ($))

Sorry if I’m explaining it poorly. As I’ve been working with it, I've come to find the actual equation is ridiculously simple but it’s as if the order or operations for the chain of the equation in wrong.

Thank you!