Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
venunm
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

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
Vvelarde
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?

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

Vvelarde
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.

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.
Vvelarde
Community Champion
Community Champion

yeah sorry, delete [] from SumDAA




Lima - Peru

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

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.

 

Capture5.PNG

 

Add target field.

 

Capture5.PNG

 

 

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 filtervisual level filter

 

Regards,

Xiaoxin Sheng

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

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

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.

Sample 1.PNG

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?

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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