cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Get SUM of compound rate calculation with numeric field

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

1 ACCEPTED SOLUTION
Super User

@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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
2 REPLIES 2
Community Support

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.

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

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.

Super User

@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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors