Forum Discussion
DAX: Running Total able to filter
Hello,
We are working to build two different running total visuals based on "Count" and "Value". We have been able to build a calculation for both (listed below), however, now those visuals do not interact with the other visuals on the tab or with any slicer.
A few key pieces to note:
- Our data set contains several different fields with date data. For this visual we are wanting to reflect count totals linked to the date known in our data set as "Completed Date". However, we did build a calendar table to use as our axis based on previous messages found in PowerBI tips.
- Our "Value" has three classes that we are wanting to visualize separately instead of net. (Positive Dollar Value, 0 Dollar Value, Negative Dollar Value). Therefore, we have created three different value calculations.
Is there a different way to calculate our running total (all time, not YTD), to allow full liked capabilities with rest of the data model?
Count Running Total:
- Running Count = COUNTROWS(FILTER(ALL(Opportunities), Opportunities[Completed Date]<=MAX(Opportunities[Completed Date])))
Our 3 Different Value Running Totals:
- Positive Dollar Value Running Total: SUMX(FILTER(ALL(Opportunities), Opportunities[Completed Date]<=MAX(Opportunities[Completed Date]) && Opportunities[Dollar Value] > 0),Opportunities[Dollar Value])
- Zero Dollar Value Running Total: SUMX(FILTER(ALL(Opportunities), AND(Opportunities[Completed Date]<=MAX(Opportunities[Completed Date]) , Opportunities[Dollar Value] = 0)),Opportunities[Dollar Value])
- Negative Dollar Value Running Total: SUMX(FILTER(ALL(Opportunities), AND(Opportunities[Completed Date]<=MAX(Opportunities[Completed Date]) , Opportunities[Dollar Value] < 0)),Opportunities[Dollar Value])
Calendar Table Includes:
Date
Day
DayofMonth
DayofWeek
Month
MonthOfYear
MonthStarting
OrdinalDate
Quarter
QuarterofYear
WeekStart
Year
For our Running Total Count Visual:
Axis: Calendar Table (MonthStarting)
Calendar Table (WeekStart)
CompletedDate (No Hierarchy)
Legend: None
Values: Running Count
(Side Note: we have our visual set on relative date filtering which is why we do not use the hierarchy)
For our Running Total Value Visual:
Axis: Calendar Table (MonthStarting)
Calendar Table (WeekStart)
CompletedDate (No Hierarchy)
Legend: None
Values: Negative Dollar Value Running Total
Zero Dollar Value Running Total
Positive Dollar Value Running Total
(Side Note: we have our visual set on relative date filtering which is why we do not use the hierarchy)
Is there a different way to calculate our running total (all time, not YTD), to allow full linked capabilities with rest of the data model? (ie Filters, Slicers & interaction with other visualizations on the tab)
Hi Cherry,
Thank you for the response! I think I may have solved by going back through some of the basics. Previously, I had the date (completedDate) in my original data set in the DAX for both running totals (count and value). When I used the below formula for the Value running total, it now interacts with my filters and slicers.
Cumulative Est. Value = CALCULATE(SUM(Opportunities[Dollar Value]), FILTER(ALL('Calendar'[Date]), 'Calendar'[Date]<=MAX('Calendar'[Date])))
Old Version/Non Working Version:
Running Totals, Value in the Area Chart, Count in the Bar Column Chart.Calendar Table.
New Versions/Working:
Working Version: Cumulative Est. Value is our "Running Total Value" measure
2 Replies
v-piga-msftResident Rockstar
Hi odummer,
Thanks for your detail description, but I still could not reproduce your issue.
We are working to build two different running total visuals based on "Count" and "Value". We have been able to build a calculation for both (listed below), however, now those visuals do not interact with the other visuals on the tab or with any slicer.
Have you created relationships between the tables? Please check the relationships.
I would appreciate it if you could share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)
Best Regards,
Cherry
Hi Cherry,
Thank you for the response! I think I may have solved by going back through some of the basics. Previously, I had the date (completedDate) in my original data set in the DAX for both running totals (count and value). When I used the below formula for the Value running total, it now interacts with my filters and slicers.
Cumulative Est. Value = CALCULATE(SUM(Opportunities[Dollar Value]), FILTER(ALL('Calendar'[Date]), 'Calendar'[Date]<=MAX('Calendar'[Date])))
Old Version/Non Working Version:
Running Totals, Value in the Area Chart, Count in the Bar Column Chart.Calendar Table.
New Versions/Working:
Working Version: Cumulative Est. Value is our "Running Total Value" measure