cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rsbin
Super User
Super User

Virtual Table within a Calculated Column

Good Afternoon,

Looking for some assistance with a Calculated Column dealing with Inventory.

Have a Table of On Hand Inventory.

Facility Item Pre-AdjustmentDate OnHandPreAdjustment Adjustment Date
ABC ATA235 04/30/2023 1 05/01/2023

 

Have a Transaction Detail Table (simplified) as follows:

Facility Item Transaction Type Date Qty.
ABC ATA235 Inventory Adjustment 05/01/2023 -1
ABC ATA235 Inventory Adjustment 05/01/2023 1

 

Want to add a Calculated Column to the first table that sums the Qty in the second table for each Facility, Item and Date match.

I have been able to do it by creating a Summary Table and Lookup. 

Facility Item Pre-AdjustmentDate OnHandPreAdjustment Adjustment Date Adjustment
ABC ATA235 04/30/2023 1 05/01/2023 0

Thinking there is a more efficient way by creating a virtual table within a Calculated Column, but haven't been able to get it to work.  Any guidance would be much appreciated.

Regards,

 

1 ACCEPTED SOLUTION
jgeddes
Solution Sage
Solution Sage

Something like this might work...

Adjustment =
var _vTable =
FILTER(
    SUMMARIZE(
        txTable,
        txTable[Item],
        txTable[Facility],
        txTable[Date],
        "sumQTY", SUM(txTable[Qty.])
    ),
    txTable[Item] = onHandTable[Item] && txTable[Facility] = onHandTable[Facility] && txTable[Date] = onHandTable[Adjustment Date]
)
Return
SUMX(
    _vTable,
    [sumQTY]
)

View solution in original post

3 REPLIES 3
jgeddes
Solution Sage
Solution Sage

Something like this might work...

Adjustment =
var _vTable =
FILTER(
    SUMMARIZE(
        txTable,
        txTable[Item],
        txTable[Facility],
        txTable[Date],
        "sumQTY", SUM(txTable[Qty.])
    ),
    txTable[Item] = onHandTable[Item] && txTable[Facility] = onHandTable[Facility] && txTable[Date] = onHandTable[Adjustment Date]
)
Return
SUMX(
    _vTable,
    [sumQTY]
)

@jgeddes ,

Thanks much for the prompt reply.

At first glance, appears to be what I am looking for.  Will give it a shot later today and let you know how I make out.

Thanks again!

Regards,

@jgeddes ,

Just wanted to let you know that I got it to work as expected.  Thanks again for the assist.

Regards,

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors