Forum Discussion

Uziel_'s avatar
Uziel_
New Member
4 years ago

Measure subtotals are incorrect

Hello,

 

I am struggling with getting this model's totals to work.  The model is importing data for various versions of a forecast.  So, there is a version for actuals, Q1 forecast, Q2 forecast, etc.  To reduce the amount of data each forecast is only contains the forecasted data and should pull from the actuals when no data is forecasted.

This is the model.

dimAccount is GL account hierarchy.
dimLevel is the location hierarchy.
dimVersion are the various versions and include the 'First Date' relevant to that version.
factData has all the actual data.
factExchange is a portion of factData split into a separate table and has exchange rates by date and location.

This is the measure that gives bad totals.  The measure pulls looks at the date in the report column and depending on how that compares to the first date of the relevant version returns either the actuals or the relevant version amounts.  The measure then looks up the conversion rate from the factExchangeRates table and multiplies the amount by the conversion rate. 

 

 

 

Translated = 
    SUMX(
        SUMMARIZE(
            factData,
            factData[Version Code],
            dimVersion[First Date],
            factData[Date],
            factData[AID],
            factData[Account Code],
            factData[Level Code],
            dimLevel[Currency Code],
            dimAccount[Display As],
            dimAccount[Multiplier],
            factData[Value],
            "converted", IF.EAGER(
                            [Date] >= [First Date],
                            SUMX(
                                factData,
                                RELATED(dimAccount[Multiplier]) * factData[Value]
                                ),
                            CALCULATE(
                                SUMX(
                                    factData,
                                    RELATED(dimAccount[Multiplier]) * factData[Value]
                                    ),
                                FILTER(
                                    ALL(dimVersion),
                                    dimVersion[Version Code]="Actuals"
                                    )
                                )
                            ) *
                            IF.EAGER(
                                dimLevel[Currency Code] = [svCurrency] || dimAccount[Display As] <> "Currency",
                                1,
                                CALCULATE(
                                    FIRSTNONBLANK(factExchangeRates[Conversion Rate],1),
                                    FILTER(
                                        RELATEDTABLE(factExchangeRates),
                                        factExchangeRates[To] = [svCurrency]
                                        )
                                    )
                                )
            ),
        [converted]
        )​

 

 

 

The Multiplier just reverses some accounts so they calculcate correctly with uses SUM().
svCurrency is a selector on the reports page where the uses chooses which currency to display.
'Display As' identifies accounts that are not convered, i.e. they are not dollars.

 

 Can anyone identify why the totals do not work correctly?
I have failed to resolve this after working on it for two weeks....  I really need to solve it and your help is very appreciated!

2 Replies

  • Is there some detail I can add to help?

    • v-easonf-msft's avatar
      v-easonf-msft
      Icon for Community Support rankCommunity Support

      Hi, Uziel_ 

      No obvious errors were found in the current formula. Please share a sample pbix for further research.

      Best Regards,
      Community Support Team _ Eason