cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
RBear Advocate I
Advocate I

Handling a one-off starting data point?

One frequently encounters situations where you start with a number and periodically increase and decrease it by certain other numbers. For example, population - increase by births, decrease by deaths; savings account - increase by interest, decrease by withdrawals, etc. In all those cases, though, there is a beginning data point - starting population, intial deposit, etc.

 

How do you coceptualize this starting data point? Let's take the savings account, for example, and say the intial deposit is 100, and I otherwise have this table:

image.png

 

 

 

 

 

 

 

 

 

 

 

 

The report looks like this:

 

image.png 

 

 

 

 

 

 

 

Obviously, somewhere in here I need to show a year-end number which will take the previous year ending balance (or, for 2016, the initial desposit) and add to it the total Activity for the year. So, in the table - where to I fit the initial deposit? And what do I do with it in the report? Do I add a column between 2016 and Activity and a row between Withdrawals and Total and stick there the intial deposit figure? And, finally, if the initial deposit is reflected in the table and report, is there a way to keep a row for total Activity for the year and another row for ending balance for that year (by that I mean, for example, that the 2016 column will show the same 10, -12, -2, but also a 98)?

 

I'm sorry if this sounds somewhat jumbled, but I asking not just about the mechanics of doing that in PBI (although I'm definitely interested in that, too), but more about the right way to think about it.

 

Thanks.

 

4 REPLIES 4
Community Support
Community Support

Re: Handling a one-off starting data point?

Hi @RBear,

 

We can create a measure as below to achieve your goal.

 

Measure = 100+ CALCULATE(SUM(Table1[Value]),ALLEXCEPT(Table1,Table1[Year]))

 

Capture.PNG

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
RBear Advocate I
Advocate I

Re: Handling a one-off starting data point?

Thanks; I'll take a look at your solution, but in the interim - FYI: when I try to open your file I get an error message to the effect that the file is incompatible with my version of PBI Desktop. I have the most recent (Version: 2.63.3272.40461 64-bit (October 2018)).

RBear Advocate I
Advocate I

Re: Handling a one-off starting data point?

Now that I have tried your measure - close, but no cigar 😞

 

First, Measure appears in the report as a column - that won't work because (i) it presents the same information in all rows, which is superflous and (ii) it has to be presented as a row. The second issue can be resolved by going to the formatting options of a Matrix. Format > Values > Show on rows > On

 

That options is better, but results in this:

image.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I don't know how to suppress the first two rows displaying Measure, such that only the bold, bottom row appears. The final version should look like this:

image.png

 

 

 

RBear Advocate I
Advocate I

Re: Handling a one-off starting data point?

And I just noticed that the measure itself doesn't work: the last column (Total Measure) should have the same values as the 2018 Measure column.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors