Forum Discussion

josephc's avatar
josephc
Frequent Visitor
9 years ago
Solved

Running Total/Calculated Sum and ignoring null values at end of list

I have the following query to summarize a list of numbers and its result   mEarnedCum = CALCULATE ( SUM ( PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH] ), FILTER ( ALLSELECTED ( P...
Need Help
  • dedelman_clng's avatar
    dedelman_clng
    9 years ago

    Try wrapping an IF looking at ActivePeriodsEarnedMH and if it is blank (ISBLANK) return 0

    mEarnedCum =
IF (
ISBLANK (PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH]),
0,
 CALCULATE (
    SUM ( PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH] ),
    FILTER (
        ALLSELECTED ( PBIScheduleSummaryPeriod ),
        PBIScheduleSummaryPeriod[PeriodID] <= MAX ( PBIScheduleSummaryPeriod[PeriodID] )
    )
 )
)

    Hope this helps

     

    David

dedelman_clng's avatar
dedelman_clng
Icon for Community Champion rankCommunity Champion
9 years ago

Try wrapping an IF looking at ActivePeriodsEarnedMH and if it is blank (ISBLANK) return 0

mEarnedCum =
IF (
ISBLANK (PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH]),
0,
 CALCULATE (
    SUM ( PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH] ),
    FILTER (
        ALLSELECTED ( PBIScheduleSummaryPeriod ),
        PBIScheduleSummaryPeriod[PeriodID] <= MAX ( PBIScheduleSummaryPeriod[PeriodID] )
    )
 )
)

Hope this helps

 

David

  • josephc's avatar
    josephc
    Frequent Visitor
    9 years ago

    It is a measure, so the, ISBLANK (PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH]),0 is throwing the typical  a singe value for column x cannot be determined, since an aggregate function is not specified.  

     

    So no luck there, good suggestion for a column though, but I need the inherit behavior of the measure for this to work

     

    • dedelman_clng's avatar
      dedelman_clng
      Icon for Community Champion rankCommunity Champion
      9 years ago

      This looks similar to an exercise I did in a DAX class.  In that case the measure was compared to 0 as the "IF" condition, and if <> 0 the calculation took place, else blank (the else was actually left out of the solution since it defaults to blank)

       

      mEarnedCum =
IF (
[ActivePeriodsEarnedMH] <> 0,
 CALCULATE (
    SUM ( PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH] ),
    FILTER (
        ALLSELECTED ( PBIScheduleSummaryPeriod ),
        PBIScheduleSummaryPeriod[PeriodID] <= MAX ( PBIScheduleSummaryPeriod[PeriodID] )
    )
 )
)

       

      If this doesn't work, can you share an anonymized version of your pbix file?

  • josephc's avatar
    josephc
    Frequent Visitor
    9 years ago

    You got me there I just had to figure out what to use in the isblank.

     

    I made a measure that simply counts the rows in each period, the massive aggregation of data behind the scenes elimiates the chances for mid project blanks and cleanly addresses the remaining project periods.

     

    mEarnedCum = 
IF (
ISBLANK ([EarnedMHPeriodCount]),
0,
 CALCULATE (
    SUM ( PBIScheduleSummaryPeriod[ActivePeriodsEarnedMH] ),
    FILTER (
        ALLSELECTED ( PBIScheduleSummaryPeriod ),
        PBIScheduleSummaryPeriod[PeriodID] <= MAX ( PBIScheduleSummaryPeriod[PeriodID] )
    )
 )