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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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-rongtiep-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-rongtiep-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-rongtiep-msft !!  Thanks so much 🙂

v-rongtiep-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-rongtiep-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!

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-rongtiep-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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.