Can't seem to get measure to sum correctly

[aistraus]

I'm trying to get [Forecast ACV (close date)] to show a grand total of all values in that column. It is instead giving me [ASP PQ] * [Projected Deals Close Date], all calculated from the grand totals of those respective measures. I've tried creating measures to do the summing in a few ways that I've found online but can't seem to crack it. Would really appreciate any help!

The current sum is 11,130,507.08

The correct sum would be 11,340,308.15.

My model is here 

Here is the formula shown in the picture:

Forecast ACV (close date) =
SUMX (
    VALUES ( 'Date Table'[Quarter] ),
    IF (
        [ElapsedBusinessDaysInQuarter] > 30,
        [Projected Deals Close Date] * [ASP],
        [Projected Deals Close Date] * [ASP PQ]
    )
)

This formula is part of it:

Projected Deals Close Date =
SUMX (
    VALUES ( 'Date Table'[Quarter] ),
    IF (
        [ElapsedBusinessDaysInQuarter] >= 45,
        [Qopps Close Date (Actual)] * [Forecast Conversion Rate (interim)],
        [Projected Qopps Close Date] * [Forecast Conversion Rate (interim)]
    )
)

[v-jingzhang]

Hi @aistraus 

Please try this measure. 

Forecast ACV (close date) new = 
sumx(
VALUES('Field - Sub Region'[Sub Region]),
if( [ElapsedBusinessDaysInQuarter] > 30,
[Projected Deals Close Date]*[ASP],
[Projected Deals Close Date]*[ASP PQ]
)
)

In addition, I think the following measure seems more correct to get the grand total result. 

Projected Deals Close Date New = 
sumx(
    VALUES('Field - Sub Region'[Sub Region]),
if([ElapsedBusinessDaysInQuarter]>=45,
[Qopps Close Date (Actual)]*[Forecast Conversion Rate (interim)],
[Projected Qopps Close Date]*[Forecast Conversion Rate (interim)]
)
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[aistraus]

Hello Jing! Thank you for your reply. I am interested in using the measure at several granularity levels and having it total appropriately for each (sub region, product, lead source). How would I set up the measure to sum appropriately for all of those granularity levels without creating 3 different measures?

[v-jingzhang]

Hi @aistraus 

Do you want to display sub region, product and lead source on rows of the same matrix visual? If so, what is their hierarchy level order? In this scenario I need to do some test to see how to modify the measure. 

If you want to display sub region, product and lead source in different visuals separately, creating 3 different measures is much easier. 

Best Regards,

Jing