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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
MasterSonic
Helper IV
Helper IV

cumulative sum or count

Hi guys,

I have 2 tables -

first with uniqe codes

second with dates and category (A,B,C)

I have created this :

 

Count of dates =
CALCULATE(
    sum('Col1'[code]),
    FILTER(
        ALLSELECTED('Col2'[Date]),
        ISONORAFTER('Col2'[Date],MAX('Col2'[Date]), DESC)
    )
)
 
I am happy with results as next screenshot shows

MasterSonic_0-1659909271323.png

 

But after appling filter on category - I have this Count 1 values.
Should not this single values add up to the rest?

Can you help me with that please ?

MasterSonic_1-1659909303398.png

 

 

 

 

5 REPLIES 5
v-yangliu-msft
Community Support
Community Support

Hi  @MasterSonic ,

You can try modifying the function to the following form:

cumulative = 
var AB = SUM('Col2'[Column])
return
CALCULATE(
SUM(
'Col1'[10]),
FILTER(
ALLSELECTED('Col2'[Date]),
'Col2'[Date]<= MAX('Col2'[Date])&&
'Col2'[Date]<>BLANK()
))

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

MasterSonic
Helper IV
Helper IV

I got this one so it works per category.🤠
I think I cannot use filter on visual level, so needed to do this.
Also I referred to dates within my table not to Date table I created previously.


Column = CALCULATE(DISTINCTCOUNT(‘Col1'[code]))

/
10 = CALCULATE(SUM('Col1'[Column]),'Col1'[Category]="C")

/

cumulative =

var AB = SUM(‘Col1’ [Column])

return

CALCULATE(

'Col1'[10],

FILTER(

ALLSELECTED('Col2'[Date]),

'Col2'[Date]<= MAX('Col2'[Date]]

 

))

)

 

/

Could you just tell me how to do not count blanks from Col2[Date] please?

 

MasterSonic_0-1659975296768.png

 

Hi @MasterSonic ,

 

Add this to the filter condition:

&&'Col2'[Date]<> blank()

 

Best Regards,

Jay

MasterSonic
Helper IV
Helper IV

Hi amitchadak,

I have added new table 

Date = CALENDAR(DATE(2015,01,01),DATE(2030,12,31))

MasterSonic_0-1659955307324.png
And applied your code (

instead of sum I have used count tho, column code has also strings within so sum doesn't work) 

 

- it works  but I would like to see smooth rise of overal values
What currenlty I have as a table view are values on the left.
My main goal is to set values like this screenshot from excel in yellow/right.

MasterSonic_1-1659955575732.png

 

amitchandak
Super User
Super User

@MasterSonic , You should always use a separate date table(Joined with date of your table) for that. and in visual use columns from that date table

 

Count of dates =
CALCULATE(
sum('Col1'[code]),
FILTER(
ALLSELECTED('Date'[Date]),
ISONORAFTER('Date'[Date],MAX('Date'[Date]), DESC)
)

 

Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41
)

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.