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
Samuele_M
Frequent Visitor

CUMULATIVE SUM by drilldown

hi, i have datas that look like the following example:

Samuele_M_0-1700496458912.png

i need to add another column to the graph with a cumulative sum of the "value" column but it has to restart from 0 on every subcategory (SCAT). i can't figure out how to do it, can someone help pls?

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Samuele_M , Try to use window function

 

Cumm Based on subcategory= CALCULATE([Your measure], Window(1,ABS,0,REL, ALLSELECTED('Table'[Category],'Table'[subcategory]),ORDERBY('Table'[subcategory],asc),,partitionBy('Table'[Category])))

 

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Samuele_M , Try to use window function

 

Cumm Based on subcategory= CALCULATE([Your measure], Window(1,ABS,0,REL, ALLSELECTED('Table'[Category],'Table'[subcategory]),ORDERBY('Table'[subcategory],asc),,partitionBy('Table'[Category])))

 

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

Ty!! this works but i have so many subcategories that it takes few minutes to load; is there any way to optimize the measure? will it be faster if i make it in powerquery following this guide? https://gorilla.bi/power-query/running-total-by-category/

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.