cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Sum of a column with repeating values - DO NOT DISPLAY

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. Power BI - issue1.PNG

1 ACCEPTED SOLUTION

Accepted Solutions

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

14 REPLIES 14
Community Champion
Community Champion

@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




Lima - Peru

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?

Community Champion
Community Champion

@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()

 

 




Lima - Peru

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

Community Champion
Community Champion

@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 ()
    )

 

 




Lima - Peru

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.

Community Champion
Community Champion

yeah sorry, delete [] from SumDAA




Lima - Peru

Hi @venunm,

 

Can you please share us a sample file to test?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

screenshot 2.PNG

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

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors