Forum Discussion
Rolling 4 hours average
Hi Experts, Add one more column "Rolling 4 hours Average", it has to map current row MIPS value + previous 3 , then divided by 4. we have a slicers to filter by region, by month, by date. ...
Hi Experts,
As per my requirement, I have to showcase rolling 4 hours average MIPS values - MIPS = Processing power of your mainframe (higher = more work being done)
I have already created a measure and applied in a new sample page with sample columns and it looks OK when I used to add with few columns with external filters as below :
1st 3 rows blank as we don't have strictly 4 rows previously (12am,1am,2am only there) to calculate 4hrs rolling, from the 4th row the calculation starts and giving the expected value as a result.
but the same measure is not working as expected when it applies to original table (More fields) as below :
it should work with or without any external filters applied .
DAX i used :
Rolling 4h Avg__ =
VAR RelationDistinct = SUMMARIZE ( ALLSELECTED ( 'VW_MF_TRANSACTIONS' ), 'VW_MF_TRANSACTIONS'[LPAR], 'VW_MF_TRANSACTIONS'[SERIAL], 'VW_MF_TRANSACTIONS'[WDID],'VW_MF_TRANSACTIONS'[CLIENT_NAME],'VW_MF_TRANSACTIONS'[MetricDateTime_1] )
VAR W = WINDOW ( -3, 0, RelationDistinct, ORDERBY ( 'VW_MF_TRANSACTIONS'[MetricDateTime_1], ASC ), PARTITIONBY ( 'VW_MF_TRANSACTIONS'[LPAR], 'VW_MF_TRANSACTIONS'[SERIAL],'VW_MF_TRANSACTIONS'[WDID], 'VW_MF_TRANSACTIONS'[CLIENT_NAME]) )
VAR Rows4 = COUNTROWS ( W ) VAR Sum4 = SUMX ( W, [MAX MIPS] )
RETURN IF ( Rows4 = 4, DIVIDE ( Sum4, 4 ), BLANK() )
RETURN IF ( Rows4 = 4, DIVIDE ( Sum4, 4 ), BLANK() )
Please help to sort this asap .
Thanks
Thanks
DK