Help with incorrect columns totals within a measure

[Dayna]

Hello,

I have a measure that calculates the value based on whether it's before, or after, the current month, this is then provided as a row total at the bottom.

Sales Forecast Quantity (Month) = 

VAR ForecastValue =
    IF (
        MAX ( 'Calendar'[MonthKey] ) < [Today (MonthKey)],
        [Actual Sales (ISS-SO)],
        [Forecast Qty]
    ) + 0
RETURN
    IF (
        HASONEVALUE ( 'Calendar'[MonthKey]  ),
        ForecastValue,
        SUMX ( VALUES ( 'Calendar'[MonthKey] ), ForecastValue )
    )

However, my column totals are incorrect, and I think it's due to the MAX in the virtual table.. So here's a sample of my data:

If we focus on the first row, the total should be 2078320, not 6060000.

If I changed the measure to:

Sales Forecast Quantity (Month) = 

VAR ForecastValue =
    IF (
        MAX ( 'Calendar'[MonthKey] ) < [Today (MonthKey)],
        [Actual Sales (ISS-SO)],
        [Forecast Qty]
    ) + 0
RETURN
    IF (
        HASONEVALUE ( 'Calendar'[MonthKey]  ),
        ForecastValue,
        SUMX ( VALUES ('Product'[pt_part]), ForecastValue )
    )

Then my row total is still not right (better, but not right):

Can someone help me, please?

Many thanks,

Dayna

[amitchandak]

@Dayna , You have the correct fact/transaction table name in place of fact. Try a measure like

Sales Forecast Quantity (Month) =

VAR ForecastValue =
IF (
MAX ( 'Calendar'[MonthKey] ) < [Today (MonthKey)],
[Actual Sales (ISS-SO)],
[Forecast Qty]
) + 0
RETURN
SUMX ( summarize (Fact, 'Product'[pt_part], 'Calendar'[MonthKey]), ForecastValue )

[Dayna]

@amitchandak thanks for the response, a silly question, but how should I set my fact table? I tried Product, which resolves the field for pt_part, but not Calendar as it doesn't have a direct relationship:

[v-chenwuz-msft]

Hi @Dayna ,

Have your question solved, if yes, please accept it as solution.

Power Bi recommend you apply star schema design principals to produce a model comprising dimension and fact tables. Or you can create a new thread about this question.                 

Best Regards

Community Support Team _ chenwu zhu

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

[Dayna]

Hello @v-chenwuz-msft sadly not, as the values are still incorrect.