cancel
Showing results for
Did you mean:
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.

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
Solution Sage

Something like this might work...

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]
)
3 REPLIES 3
Solution Sage

Something like this might work...

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]
)
Super User

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,

Super User

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

Regards,

Announcements

#### Join Arun Ulag at MPPC23

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

#### Power BI September 2023 Update

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

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### 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
Top Kudoed Authors