## Desktop

Highlighted
Regular Visitor
Posts: 37
Registered: ‎04-21-2017

# How to fill in blank field in accumulated table?

I calculated the accumulated product units and made below table. And I want to create a scatter table and use year as the play axis and shows the products as legend. But, since in the scatter chart, play axis ends at 2017, some products disappear in the end due to they don't have numbers in 2017. How can I keep the 2017 number in the chart when it ends.

Accepted Solutions
Super Contributor
Posts: 3,483
Registered: ‎09-27-2016

## Re: How to fill in blank field in accumulated table?

Hi @Gracie,

`Running Total Units = CALCULATE(count(Deal[Proudct]),filter(all('Calendar'),'Calendar'[Date] <= MAX('Calendar'[Date])))+0`

Best Regards,
Angelia

All Replies
Super User
Posts: 7,180
Registered: ‎07-11-2015

## Re: How to fill in blank field in accumulated table?

[ Edited ]

Do you mean keep the final amount, like for the products that have a last number in 2014? Keep that in the chart even for 2017? You might try creating a measure that does an accumulation that essentially sums all values from that year and less?

Proud to be a Datanaut!

Regular Visitor
Posts: 37
Registered: ‎04-21-2017

## Re: How to fill in blank field in accumulated table?

I already created the accumulation measure. The value in the table uses the measure. But the field will be blank if there's no units in that year. That's why some fields in 2017 and other years are blank. I want to fill up with units amount even if there are no units in that year.

The accumulation measure I used is this:

Running Total Units = CALCULATE(count(Deal[Proudct]),filter(all('Calendar'),'Calendar'[Date] <= MAX('Calendar'[Date])))

Super Contributor
Posts: 3,483
Registered: ‎09-27-2016

## Re: How to fill in blank field in accumulated table?

Hi @Gracie,

`Running Total Units = CALCULATE(count(Deal[Proudct]),filter(all('Calendar'),'Calendar'[Date] <= MAX('Calendar'[Date])))+0`

Best Regards,
Angelia

Regular Visitor
Posts: 37
Registered: ‎04-21-2017

## Re: How to fill in blank field in accumulated table?

[ Edited ]

It doesn't work. It just puts 0 in the blank fields, not rolling the previous number to the current year.

Super Contributor
Posts: 3,483
Registered: ‎09-27-2016

## Re: How to fill in blank field in accumulated table?

Hi @Gracie,

Do you mind share your sample data or .pbix file for further analysis? So I can post the solution which is close to your requirement.

Best Regards,
Angelia

Regular Visitor
Posts: 37
Registered: ‎04-21-2017

## Re: How to fill in blank field in accumulated table?

I tried myself with the same data from excel data source. I got the result I want.

But I still can't get the same result from sql server data souce. The methods are the same. It's weird.

https://app.powerbi.com/groups/me/reports/a139c91a-5871-414c-b2da-9ea5e792b7ee/ReportSection5

how can I share my phix file?

Super Contributor
Posts: 3,483
Registered: ‎09-27-2016

Hi @Gracie,