Sum of a column with repeating values - DO NOT DISPLAY

[venunm]

Hi

In the attached how would I not display the sum value 837418.20 for the rest of the days where there are no actual values yet? Please see THE FORMULA BELOW  and you would understand what I am trying to do.

[Vvelarde]

@venunm

hi, Transform your dax to this:

Measure=IF (Column <>Blank(),Calculation, Blank())

In your case Column is NewColumn.Disbursed Advance Amount

And Measure is Total PP This Month

[venunm]

Hi thanks for getting back to me on this. What is 'calculation' in the If statement you used below?

Also my NewColumn.Disbursed Advance Amount is not populating when I start the formula?

[Vvelarde]

@venunm

try this:

Total PP This Month=If(Calculate(Sum(Merge1[NewColumn.Disbursed Advance Amount])<>Blank),Calculate(Sum(Merge1[NewColumn.Disbursed Advance Amount]),Filter(ALL-------------); Blank()

[venunm]

Hey still not getting thru...I might be doing something really silly....Please see below

Column = IF(CALCULATE(sum(Merge1[NewColumn.Disbursed Advance Amount])<>BLANK),CALCULATE(sum(Merge1[NewColumn.Disbursed Advance Amount]),FILTER(ALL(Merge1),Merge1[Days into Quarter]<MAX(Merge1[Days into Quarter]))); BLANK()

[Vvelarde]

@venunm

Try changing this to simplify the dax formula:

Mymeasure =
VAR SumDAA =
    CALCULATE ( SUM ( Merge1[NewColumn.Disbursed Advance Amount] ) )
RETURN
    IF (
        [SumDAA] <> BLANK (),
        CALCULATE (
            [SumDAA],
            FILTER (
                ALL ( Merge1 ),
                Merge1[Days into Quarter] < MAX ( Merge1[Days into Quarter] )
            )
        ),
        BLANK ()
    )

[venunm]

it says the value for 'SUMDAA' cannot be determined. Either 'SumDAA' doesnt exist, or there is no current row for a column named 'SumDAA'?

I have tried using both new measure and new column.

[Anonymous]

Hi @venunm,

Can you please share us a sample file to test?

Regards,

Xiaoxin Sheng

[Vvelarde]

yeah sorry, delete [] from SumDAA

[venunm]

Hi, so as you can see from the above, the 'My measure' formula is doing the job of not repeating the max value till 31st January but adding up incorrectly. It should be cumulative i.e. Jan 4th should start from 41,000, Jan 5th should be newcolumn.disbursed advance amount 168,139.69 + 41,000 = 209,139.69 and so on. The values should be same as the 'Total PP this month' column. Is it something to do with the formula?

Formula used is:

My Measure = VAR SumDAA =
    CALCULATE ( SUM ( Merge1[NewColumn.Disbursed Advance Amount] ) )
RETURN
    IF (
        SumDAA <> BLANK (),
        CALCULATE (
            SumDAA,
            FILTER (
                ALL ( Merge1 ),
                Merge1[Days into Quarter] < MAX ( Merge1[Days into Quarter] )
            )
        ),
        BLANK ()
    )

[Anonymous]

Hi @venunm,

Based on my understanding, you want to hide the blank row of the "NewColumn.Disbursed Advance Amount" column , right?

If this is a case, you can refer to below formula if it works on your side:

Measure:

RollingTotal = 
CALCULATE(SUMX(FILTER(ALL(Sheet1),[Date]<=MAX([Date])),[Current]),FILTER(Sheet1,Sheet1[Current]<>BLANK()))

Result:

Drag measure and current.

Add target field.

BTW, the best simple way to hide the blank records is use the visual level filter.(all function cannot cancel this filter affect)

visual level filter

Regards,

Xiaoxin Sheng

[venunm]

Hi do you know how you got the 'current' in you formula? and what is dates?

[Anonymous · Accepted solution]

Hi @venunm,

>>Hi do you know how you got the 'current' in you formula? and what is dates?

"Current" is the " New Column.Disbursed Advance Amount" column, "Data" field has auto analyzed to date hierarchy.

Regards,

Xiaoxin Sheng

[venunm]

Hi also, I have the data in the table displayed by day as above screenshot. How do I add a measure with rolling weekly amounts displayed by week so I can graph the data to show rolling progress by week?

[venunm]

EXCELLENT! THIS WORKS. Thank you for all the help.