Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
BoSe
Frequent Visitor

Get SUM of compound rate calculation with numeric field

Hi all,

 

please see below one example data set:

YearValueFC-Flag
201023 
201143 
201223 
201345 
201467 
201554 
2016541
2017541
2018541
2019541
2020541
2021541
2022541
2023541

 

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.

BoSe_0-1708198491463.png

 

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
Greg_Deckler
Super User
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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
v-zhengdxu-msft
Community Support
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.

 

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.

Greg_Deckler
Super User
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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors