cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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.

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors