Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Viranga
Frequent Visitor

How to calculate the value scaling down automatically

Viranga_2-1666954718728.png

 

The current Stock count will get reduced against each Used Item Quantity.

Requirement - generate the current stock automatically(after each item quantity reduction) 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.