Forum Discussion
Cumulative count for missing data
Hello,
I am trying to figure out how to calculate a cumulative count where, if a category returns 0, it will take the "previous" value. I have tried a couple methods but it doesn't seem to work. This must be a measure due to the size of the data set.
This is my current DAX:
Cumulative Tester =
VAR _Count =
CALCULATE (
DISTINCTCOUNT ( Data[OrderId] ),
FILTER (
ALLSELECTED ( Data ),
Data[DaystoRespond] <= MAX ( Data[DaystoRespond] )
)
) + 0
VAR _Total =
CALCULATE ( DISTINCTCOUNT ( Data[OrderId] ), ALLSELECTED ( Data) )
VAR _AccountForZero =
CALCULATE (
DISTINCTCOUNT ( Data[OrderId] ),
FILTER (
ALLSELECTED ( Data ),
( Data[DaystoRespond] - 1 )
<= MAX ( Data[DaystoRespond] ) - 1
)
) + 0
VAR _NoBlankCount =
IF ( _Count = 0, _AccountForZero, _Count )
RETURN
// DIVIDE(_NoBlankCount,_Total)
_AccountForZero
This gives me the following result:
The intended result should be that, on row 26, the result should be 12948, instead of 0.
Would anyone be able to help?
Thanks!
2 Replies
- amitchandakSuper User
ExcelMonke , Days to respond or bucket should come from seperate table. that will offer better result. Measn has a dimension
CALCULATE (
DISTINCTCOUNT ( Data[OrderId] ),
FILTER (
ALLSELECTED ( DaystoRespond),
DaystoRespond[DaystoRespond] <= DaystoRespond( Data[DaystoRespond] )
)
)Or use window function, make sure all groupby of visual are using in window's relation
You can alos use new visual calculation runningsum
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-3d98a5b0e07fVisual Calculations in Power BI- February 2024 Update RUNNINGSUM, RANGE, MOVINGAVERAGE, COLLAPSE, COLLAPSEALL, EXPAND, EXPANDALL, FIRST, LAST, PREVIOUS, and NEXT
https://www.youtube.com/watch?v=bKD9T0EWgQo&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L- ExcelMonkeImpactful Individual
Hello,
Thanks for the suggestion. For some context: I do have an additional table that is for Day bucket. This links to my data table which has an additional column which rounds the 'Data'[DaystoRespond] column.
So - this connection is a One:Many from DayBucket[Bucket] to Data[DaystoRespond_Bucket].
Unfortunatey, the above recommended DAX did not work.