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
12sarah96
Regular Visitor

Cumulative Grand & Row Totals Not Correct

Hi, 

 

I am building a matrix which is to show the cumulative sales total of various categories and products.

 

I have found a way to get my values to cumulate down to that level, but where I'm currently stuck is with the row and grand row totals. They seem to be duplicating the line above (assuming because of the MAX).

 

Is there a better way to write this DAX formula to achieve this ask? Or is it a small change that needs to be made for the totals to work properly? 

 

I do have a date table built into the report and the snip below is showing the product index number for privacy reasons. Product_Level_2 what is normally shown.

 

Thank you!

 

 

v2 - Enterprise Sales | TY Cumulative = CALCULATE(
    [Enterprise Sales | TY Actuals],
    FILTER(ALL('C+D | Enterprise Sales Total'),'C+D | Enterprise Sales Total'[Date]<=max('C+D | Enterprise Sales Total'[Date])  &&
    'C+D | Enterprise Sales Total'[New/Existing Clients]=max('C+D | Enterprise Sales Total'[New/Existing Clients]) &&
    'C+D | Enterprise Sales Total'[PRODUCT_LEVEL_2]=MAX('C+D | Enterprise Sales Total'[PRODUCT_LEVEL_2])))

 

 

12sarah96_1-1713989515517.png

 

  

1 REPLY 1
amitchandak
Super User
Super User

@12sarah96 , You should use a date table joined with the date of your table. The field from date table should be used in a filter, slicer, measure, and visual

 

Have measure with all filters other than date, say M1 

and then have measures like

 

Cumm Sales = CALCULATE([M1],filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE([M1],filter(allselected(date),date[date] <=max(date[Date])))

Cumm Based on Date = CALCULATE([M1], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

Cumm Based on Date = CALCULATE([M1], Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))

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

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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