cancel
Showing results for
Did you mean:

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

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])))``````

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:

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors