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

cumulative totals for some projects that have started and some that haven't

BI File: https://www.dropbox.com/s/rbjoqspdjdmtquq/Power%20BI%20Forum%20Sample.pbix?dl=0

Excel Data: https://www.dropbox.com/s/i95ssp7bjxcglzt/Portfolio%20Tool%20-%20Standardized%20Multi%20Asset%20Mode...

 

I am setting up BI for projects, some of which start in the future and some of which have already started.  The data supporting this is essentially flattened, which I'm recognizing is a problem, but haven't had the time to figure out how to pivot it to work nicely with BI and my understanding of the new programming.

 

So what I'm trying to achieve, using the table below as a very simplified example, is to return a figure of 27,500, which would be the current debt of projects that have started, plus the starting debt of projects that haven't yet started.

 

Again, my data is flattened, so the "GROUPBY" or "SUMMARIZE" is necessary and I've used them, but only really via cut/paste to make them work rather than a deep understanding of what they're actually doing.  

 

 Project StartStarting DebtCurrent Debt
 10/31/201890008500
 3/31/2019150000
 6/30/201940000
TOTAL 280008500
1 ACCEPTED SOLUTION

Accepted Solutions
moumipanja Regular Visitor
Regular Visitor

Re: cumulative totals for some projects that have started and some that haven't

1. Create a measure which will sum up the current debt of projects that have started

Measure1 = CALCULATE(SUM('Sheet2 (2)'[Current Debt]),FILTER('Sheet2 (2)','Sheet2 (2)'[Project Start]<=TODAY()))

2. Create a measure which will sum up the starting debt of projects that have not yet started

Measure2 = CALCULATE(SUM('Sheet2 (2)'[Starting Debt]),FILTER('Sheet2 (2)','Sheet2 (2)'[Project Start]>TODAY()))

3. Create another measure that will sum up the above two measures and display the result in a Card 

Measure = [Measure1]+[Measure2]

You will the following result

1.JPG

2 REPLIES 2
moumipanja Regular Visitor
Regular Visitor

Re: cumulative totals for some projects that have started and some that haven't

1. Create a measure which will sum up the current debt of projects that have started

Measure1 = CALCULATE(SUM('Sheet2 (2)'[Current Debt]),FILTER('Sheet2 (2)','Sheet2 (2)'[Project Start]<=TODAY()))

2. Create a measure which will sum up the starting debt of projects that have not yet started

Measure2 = CALCULATE(SUM('Sheet2 (2)'[Starting Debt]),FILTER('Sheet2 (2)','Sheet2 (2)'[Project Start]>TODAY()))

3. Create another measure that will sum up the above two measures and display the result in a Card 

Measure = [Measure1]+[Measure2]

You will the following result

1.JPG

mrothschild Member
Member

Re: cumulative totals for some projects that have started and some that haven't

Thanks for your help - because my data is flattened your solution didn't quite work exactly, but it got me where I needed to be.  I needed to add a filter to a calculate so the formula looks like this which is essentially 3 measures:  (A) total starting debt outstanding per project, (B) starting debt outstanding for projects that have already started and (C) Current outstanding debt for projects that have already started.  

 

The outcome is (A) - (B) + (C).  So in the example from the table I provided earlier would be 28,000 - 9,000 + 8,500 = 27,500 to get to the same outcome.

 

Debt Outstanding on Deployed and Pipeline Deals =
CALCULATE(
SUMX(
GROUPBY('_Bronn Portfolio Analysis Table','_Bronn Portfolio Analysis Table'[Asset ID],'_Bronn Portfolio Analysis Table'[Debt Outstanding]),
'_Bronn Portfolio Analysis Table'[Debt Outstanding]
),
FILTER('_Bronn Portfolio Analysis Table','_Bronn Portfolio Analysis Table'[Count]=0)
)

-
 
CALCULATE(
SUMX(
GROUPBY('_Bronn Portfolio Analysis Table','_Bronn Portfolio Analysis Table'[Asset ID],'_Bronn Portfolio Analysis Table'[Debt Outstanding]),
'_Bronn Portfolio Analysis Table'[Debt Outstanding]
),
FILTER('_Bronn Portfolio Analysis Table','_Bronn Portfolio Analysis Table'[Count]=0),
FILTER('_Bronn Portfolio Analysis Table','_Bronn Portfolio Analysis Table'[Lease Start Date]<today())
)

+

SUMX (
FILTER (
'_Bronn Portfolio Analysis Table',
DATE ( YEAR ( '_Bronn Portfolio Analysis Table'[Calendar Month] ), MONTH ( '_Bronn Portfolio Analysis Table'[Calendar Month] ), 1 )
= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
),
'_Bronn Portfolio Analysis Table'[Debt Outstanding]
)

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 114 members 1,396 guests
Please welcome our newest community members: