Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Sam_Meyer
Regular Visitor

Gaps in chart data when calculating cumulative total

‎04-03-2024 02:45 PM

I have a chart based on a running list of transfers for different funding cycles which can fall into 3 divisions. These transfers are adjustments to what we call our 'base' for the year and make up our $ expense targets - I am using a cumulative measure using MAX to sum all activity up to and before each funding cycle based on ranking to keep track of our $ expense target throughout the cycles:

SUMADJ = CALCULATE(SUM('Target Tracker Data'[$ Amount]))
 
CUMTarget = CALCULATE([SUMADJ],FILTER(ALL('Target Tracker Data'),'Target Tracker Data'[Cycle #]<=MAX('Target Tracker Data'[Cycle #])),FILTER(ALL('Target Tracker Data'),'Target Tracker Data'[Impact Year]=SELECTEDVALUE('Target Tracker Data'[Impact Year])),FILTER(ALL('Target Tracker Data'),'Target Tracker Data'[Division]=SELECTEDVALUE('Target Tracker Data'[Division])))

 

I am using a stacked area chart with total value at the top (expense target) - my issue is that if a particular division does not have funding activity within a specific cycle, there is no data populated for that specific cycle/division which breaks the specific divisions line in the chart and does not provide an accurate total either. How can I return the previous cycles value for divisions that do not have funding activity within a spcific cycle?

 

Sam_Meyer_0-1712179442120.png

Sam_Meyer_0-1712180510364.png

 

Thank you

 

Labels:
Message 1 of 9
504 Views
0
8 REPLIES 8
Ashish_Mathur
Super User
Super User

‎04-03-2024 08:15 PM

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result in a Table format.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 4 of 9
451 Views
0
Sam_Meyer
Regular Visitor
In response to Ashish_Mathur

‎04-04-2024 08:47 AM

Desired Output - sorry for double reply I was exceeding character limit

DivisionBaseBudget V1Budget V3Budget V52+105+7
56219458222254672723645822236458222439750024397500
V157722623568900405712604058263101.563956664.765970579.7
V21034042498912649350264935026497383809738380

 

Message 6 of 9
435 Views
0
Ashish_Mathur
Super User
Super User
In response to Sam_Meyer

‎04-04-2024 04:45 PM

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1712274282300.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Stacked area chart.pbix
Message 7 of 9
408 Views
Sam_Meyer
Regular Visitor
In response to Ashish_Mathur

‎04-11-2024 09:17 AM

Hi Ashish, 

 

Thank you for the reply and apologies for my delayed response - I had to wait for an updated version of Power BI to open your file. 

 

I am running into one more issue - depending on the year, we can have cycles ranging from base all the way to 11+1 so my 'Forecast Cycle Order' table looks like this (we would like users to be able to select different years):

Sam_Meyer_1-1712851717332.png

 

If I include the 2023 data I have, which has 8+4 cycle data points as well, my calendar table looks like this: 

Sam_Meyer_2-1712851883099.png

 

Visualization below:

Sam_Meyer_3-1712852001912.png

 

How would I get the visualization to not include forecast cycles in the X-axis which do not have any activity in the selected year, given the x-axis is driven by the calendar table?

 

For example: The 2024 data I provided only had data points related to Base, Budget V1, Budget V3, Budget V5, 2+10, and 5+7

 

Thank you again

 

 

Message 8 of 9
364 Views
0
Ashish_Mathur
Super User
Super User
In response to Sam_Meyer

‎04-11-2024 08:08 PM

I cannot understand your question.  Someone who does will help you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 9 of 9
344 Views
0
Sam_Meyer
Regular Visitor
In response to Ashish_Mathur

‎04-04-2024 08:45 AM

Data

$ AmountDivisionImpact YearForecast CycleCycle #
1150000562024Budget V11
150000V12024Budget V11
455000V12024Budget V11
90000V12024Budget V11
130000V12024Budget V11
(107657)V12024Budget V11
(284886)V12024Budget V11
(115230)V12024Budget V11
(263419)V12024Budget V11
(165408)V12024Budget V11
(527738)V12024Budget V11
(44218)V12024Budget V11
(840420)562024Budget V11
(108675)562024Budget V11
(449160)V22024Budget V11
(150000)V12024Budget V11
338524V12024Budget V11
300000V12024Budget V11
36378V12024Budget V11
200000562024Budget V11
200000562024Budget V11
325000V12024Budget V11
(342124)V12024Budget V11
(298733)V12024Budget V11
(358073)V12024Budget V11
180000V12024Budget V32
(505000)V22024Budget V32
(36000)V22024Budget V32
31000V12024Budget V32
150000562024Budget V32
(150000)V12024Budget V32
840420562024Budget V32
108675562024Budget V32
175000V12024Budget V32
500000V12024Budget V53
500000V12024Budget V53
24092V12024Budget V53
112969V12024Budget V53
44760V120242+105
135580V220242+105
65005620242+105
137804V120242+105
70390V120242+105
40420V120242+105
561045620242+105
42000V120242+105
600005620242+105
(17064)V120242+105
233865V120242+105
160500V120242+105
(14500)V120242+105
50000V120242+105
286755620242+105
200000V120242+105
148536V220242+105
(490500)V120242+105
230238V120242+105
6003995620242+105
(420000)V120242+105
272000V120242+105
125000V120242+105
852000V120242+105
104000V220242+105
1000000V120242+105
1335000V120242+105
1841650V120242+105
232000V120245+78
281915V120245+78
(150000)V120245+78
1650000V120245+78
57722623V12024Base0
10340424V22024Base0
21945822562024Base0
Message 5 of 9
435 Views
0
Anonymous
Not applicable

‎04-03-2024 07:01 PM

Hi @Sam_Meyer 

 

Maybe you can try isblank() and if() function, the logical can be like if the target is blank, return the previous cycles value.

Here are links of these two function:

IF function (DAX) - DAX | Microsoft Learn

ISBLANK function (DAX) - DAX | Microsoft Learn

 

If the above one can't help you, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Message 2 of 9
462 Views
0
Sam_Meyer
Regular Visitor
In response to Anonymous

‎04-04-2024 10:17 AM

Hi, I have tried to use a cobination of IF and ISBLANK, but the problem is without a data point for a specific Division/Forecast Cycle cross-section it seems as if the ISBLANK has no data point to evaluate. The output is the exact same. This scenario only works if I manually add a missing cross-section which I do not want to do.

 

As for attaching a simplified pbix - DropBox can't seem to load it after 5 hours of waiting, I can't share files outside my organization in OneDrive, and google drive can't seem to upload it either. Could I possibly email it to you lol. 

 

If not, please check my replies to Ashish - do not want to include here because it is long. Apoligies for formatting I couldn't seem to get it right, but it's pasteable. The only imprtant logic is included in my original post and I am not sure what you mean by special examples.

Message 3 of 9
423 Views
0

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

View All