Forum Discussion

Viranga's avatar
Viranga
Frequent Visitor
3 years ago
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) 
General Comment
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    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 + 0

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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 + 0

Regards,

Xiaoxin Sheng