Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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:
Our 3 Different Value Running Totals:
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)
Solved! Go to Solution.
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:
New Versions/Working:
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:
New Versions/Working:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |