total value for a calculated measure column is not correct

[sjoulaei]

 Hi all,

sorry if this is a repeated question. I went through few other threads that looked similar, but still haven't been able to work it out. I am new to Power BI and still getting my head around the wonderful tool. Any help appreciated.

What I want to achieve

Prepare a financial forecasting tool that will update the future outlook based on altering the assumptions about varioud things (screenshot attached).

What I have done

I created a single table that includes the Years that I want to include in the report.

Added bunch of What-if parameters that will be used to play with the forecasts

Then everything else is measure calculated with PRODUCTX() on that table.

For example, Annual Revenue formula is as below:

Year Revenue = PRODUCTX(
                       'Finance Forecast',
                       'Base Revenue'[Base Revenue Value] * POWER(1+[Expected Growth Rate Value], 'Finance Forecast'[Year Index]-1)
                        )

Financial Forecast table is as below:

Questions:

• Is this the best way to achieve what I want to get?
• How to fix the totals column? 

[Ashish_Mathur · Accepted solution]

Hi,

Does this work?

=IF(HASONEVALUE([Year]),[Year Revenue],SUMX(SUMMARIZE(VALUES([Year]),[Year],"Rev",[Year Revenue]),[Rev]))

[sjoulaei]

Thanks for quick respond @Ashish_Mathur 

That did the trick 🙂

I combined the two in one measure as below:

Year Revenue = IF(HASONEVALUE(Years[Year]),
                    PRODUCTX(
                            'Years',
                            'Base Revenue'[Base Revenue Value] * POWER(1+[Expected Growth Rate Value], 'Years'[Year Index]-1)
                            ),
                    SUMX(SUMMARIZE(VALUES(Years[Year]),
                                   Years[Year],
                                   "Rev",
                                    PRODUCTX(
                                            'Years',
                                            'Base Revenue'[Base Revenue Value] * POWER(1+[Expected Growth Rate Value], 'Years'[Year Index]-1)
                                            )
                                    ),
                        [Rev])
                    )

and now trying to digest what just happened haha

[Ashish_Mathur]

You are welcome.