Forum Discussion
Solved
How to calculate the value scaling down automatically
The current Stock count will get reduced against each Used Item Quantity.
Requirement - generate the current stock automatically(after each item quantity reduction)
HI Viranga,
Current power bi matrix visual does not support direct aggregate and calculating these values, I'd like to suggest you write a measure formula to look up and calculate with corresponding values and use on the matrix:
formula = VAR currDate = MAX ( Table[date] ) VAR currStock = CALCULATE ( SUM ( Table[Stock] ), ALLSELECTED ( Table ), VALUES ( Table[Order] ), VALUES ( Table[Item] ) ) VAR totalUsed = CALCULATE ( SUM ( Table[Used Item] ), FILTER ( ALLSELECTED ( Table ), [Date] < currDate ), VALUES ( Table[Order] ), VALUES ( Table[Item] ) ) RETURN currStock - totalUsed + 0Regards,
Xiaoxin Sheng
1 Reply
HI Viranga,
Current power bi matrix visual does not support direct aggregate and calculating these values, I'd like to suggest you write a measure formula to look up and calculate with corresponding values and use on the matrix:
formula = VAR currDate = MAX ( Table[date] ) VAR currStock = CALCULATE ( SUM ( Table[Stock] ), ALLSELECTED ( Table ), VALUES ( Table[Order] ), VALUES ( Table[Item] ) ) VAR totalUsed = CALCULATE ( SUM ( Table[Used Item] ), FILTER ( ALLSELECTED ( Table ), [Date] < currDate ), VALUES ( Table[Order] ), VALUES ( Table[Item] ) ) RETURN currStock - totalUsed + 0Regards,
Xiaoxin Sheng