topic Re: Get SUM of compound rate calculation with numeric field in DAX Commands and Tips

Get SUM of compound rate calculation with numeric field

BoSe — Sat, 17 Feb 2024 19:44:58 GMT

Hi all,

please see below one example data set:

Year	Value	FC-Flag
2010	23
2011	43
2012	23
2013	45
2014	67
2015	54
2016	54	1
2017	54	1
2018	54	1
2019	54	1
2020	54	1
2021	54	1
2022	54	1
2023	54	1

With that data i do calculate firstly a measure that displays only the Forecast Values:

FC = CALCULATE(SUM(Tabelle1[Wert]),NOT ISBLANK(Tabelle1[Flag]))

That measure i use to calculate the actual compound interest values in combination with a Numeric Range user input (Index Value):

Wage Indexed = VAR selectedyear = SELECTEDVALUE('Date'[Year]) VAR currentyear = 2015 // this might need to be calculated VAR rate = DIVIDE([Index Value],100) RETURN [Wages] * POWER((1 + rate),selectedyear-currentyear)

Thats works fine for displaying the values in a table/chart and the values are calculated as expected. However when I try to get the sum over the years this measure returns 0 - and i didn't manage to adjust the query to also return a sum over the years.

Example screenshot: I'm trying to get the SUM for the values in each year in the Total(Gesamt) column. As you can see currently, it doesn't work like that.

Would appreciate any help/idea how to get the sum in combination with calculating the compound interest with a numeric field (user input). Thanks

Re: Get SUM of compound rate calculation with numeric field

Greg_Deckler — Sat, 17 Feb 2024 21:54:20 GMT

BoSe First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Re: Get SUM of compound rate calculation with numeric field

Anonymous — Mon, 19 Feb 2024 08:06:41 GMT

Hi BoSe

The first solution for incorrect totals is move from a measure to a calculated column, the calculated column do exactly what we want: calculate the incentive on each row, and then sum up the row for the total.

The second solution is that create a summarize table and use sumx function.

The error you met is that the logical text is total[Wages] * total(POWER((1 + rate),selectedyear-currentyear)) and the total row can't get the value of the total of the wages or total(POWER()1+rate),selectedyear-currentyear).

The summerize table or Iterative functions will sum up the result of every single row of the virtual table that we have created, so that we can get the correct result.

These links may help:

Why Your Total Is Incorrect – A Key Power BI Concept | Master Data Skills + AI (enterprisedna.co)

Power BI: Totals Incorrect and how to Fix it - Finance BI (finance-bi.com)

Fixing Incorrect Totals Using DAX Measures In Power BI | Master Data Skills + AI (enterprisedna.co)

Solved: How to calculate the average by dividing instead o... - Microsoft Fabric Community

If the above one can't help you, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

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