cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
redg
New Member

Calculating PV as a Measure

This is my first post on literally any user group, so go easy on me 🙂

 

I am building a dashboard that calculates PV for a group of accounts.  I'm attempting to leverage What-If parameters to allow the user to interact with the rate for that calculation, and because of that I am left writing the following measure:

 

gross_funding_am = PV(((wavg_discount_rt[wavg_discount_rt Value] / 100) / 12), average(myTbl[term]), average((myTbl[payment])) * -1, sum(myTbl[expected_balloon]) * -1)
 
This seems to yield the correct results on a row level, but the aggregate level gets a bit wonky.  For context on the picture below, the resulting total should be higher than that of the acct_loan_am field: 
 
redg_2-1656102630643.png

 

 I know other users have posted in the past for simpler issues and the answer was something using SUMX, but for something that (in my mind) is more complicated like a PV function, how would I fix this so that it totals properly?
 
 

 

1 ACCEPTED SOLUTION
v-polly-msft
Community Support
Community Support

Hi @redg ,

Please refer to the pbix file to see if it helps you.

Modify the measure.

gross_funding_am_sum =
VAR _b =
    SUMMARIZE ( 'Sheet1', Sheet1[account_id], "aaa", [gross_funding_am] )
RETURN
    IF ( ISINSCOPE ( Sheet1[account_id] ), [gross_funding_am], SUMX ( _b, [aaa] ) )

vpollymsft_0-1656552735802.png

Best Regards

Community Support Team _ Polly

 

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

View solution in original post

9 REPLIES 9
v-polly-msft
Community Support
Community Support

Hi @redg ,

Please refer to the pbix file to see if it helps you.

Modify the measure.

gross_funding_am_sum =
VAR _b =
    SUMMARIZE ( 'Sheet1', Sheet1[account_id], "aaa", [gross_funding_am] )
RETURN
    IF ( ISINSCOPE ( Sheet1[account_id] ), [gross_funding_am], SUMX ( _b, [aaa] ) )

vpollymsft_0-1656552735802.png

Best Regards

Community Support Team _ Polly

 

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

This was it @v-polly-msft !!  Thanks so much 🙂

v-polly-msft
Community Support
Community Support

Hi @redg ,

Please have a try

Create a measure.

Measure =
VAR _a = [gross]
VAR _b =
    SUMMARIZE ( 'Table', 'Table'[Index.1], "aaa", _a )
RETURN
    IF ( HASONEVALUE ( 'Table'[Index.1] ), _a, SUMX ( _b, [aaa] ) )

The gross measure stands for gross_funding_am. You can use the id column replace index.1 column.

vpollymsft_0-1656386375386.png

 

Best Regards

Community Support Team _ Polly

 

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

Hi @v-polly-msft !

 

Thanks for taking the time to respond.  I'm not sure that i'm understanding your solution and associated image properly.  I think I'm not understanding something about your solution - I would expect the sum of the gross_funding_am field to be $17,883.33, not $23,452.20.  I've also tried to implement your idea and i'm getting a sum of $32,980.99 (which further entrenches my belief that I'm not understanding what you mean).  Can you elaborate a bit?

 

Thanks!

v-polly-msft
Community Support
Community Support

Hi @redg ,

It seems that the total value of measure is not correct, right? This document has a detailed explanation.

Please refer to.

Dealing with Measure Totals 

There is also a post, please refer to it to see if it helps you.

How to remove duplicates count 

 

If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information (Or some sample data).

 

Best Regards

Community Support Team _ Polly

 

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

hi @v-polly-msft,

 

I'm attempting to share the PBIX file in a link here.  Forgive me if this goes sideways... first time.  

 

testfile.pbix 

Ashish_Mathur
Super User
Super User

Hi,

In the total row, do ou want to add the figure appearing int the table visual above?  Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur !

 

I would expect the total of the gross_funding_am column to equal 17,883.33 but that's not happening at the moment.  I'm not entirely sure how to upload my pbix file here but as soon as I do I will follow-up.

Hi @Ashish_Mathur 

 

I think I figured out the uploading.  Here is the link:

 

testfile.pbix 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors