Forum Discussion

Sam_Meyer's avatar
Sam_Meyer
Regular Visitor
2 years ago

Gaps in chart data when calculating cumulative total

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?

 

 

Thank you

 

Need Help

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

    • Sam_Meyer's avatar
      Sam_Meyer
      Regular Visitor
      2 years ago

      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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

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

    • Sam_Meyer's avatar
      Sam_Meyer
      Regular Visitor
      2 years ago

      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
    • Sam_Meyer's avatar
      Sam_Meyer
      Regular Visitor
      2 years ago

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

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