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
Gracie
Helper II
Helper II

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.

 

 

Capture.PNG

1 ACCEPTED SOLUTION

Hi @Gracie,

Please add 0 in your measure and check if it works fine.

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


Please don't hesitate to ask if you have any other problem.

Best Regards,
Angelia

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

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?

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Hi @Gracie,

Please add 0 in your measure and check if it works fine.

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


Please don't hesitate to ask if you have any other problem.

Best Regards,
Angelia

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

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

Sorry for late reply, v-huizhn-msft.

 

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. 

I attached the .phix for your check. Thanks for your help.

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

 

how can I share my phix file?

 

Hi @Gracie,

Please save your pbix file, and load it as attachments like the screenshot below.

1.PNG

Or you can load it to OneDrive, and post the share link.

Thanks,
Angelia

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.