cancel
Showing results for
Did you mean:
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:

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
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] ) )
``````

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.

9 REPLIES 9
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] ) )
``````

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.

New Member

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

Community Support

Hi @redg ,

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.

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.

New Member

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!

Community Support

Hi @redg ,

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

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

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.

New Member

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

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
New Member

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.

New Member

Announcements