cancel
Showing results for
Did you mean:

## 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:

The report looks like this:

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

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.

## 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)).

## 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:

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:

## 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.

Announcements

#### 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!

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

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.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors