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
Anonymous
Not applicable

Average inventory and cost balances for large transaction table

Hi,

I have a fairly large transaction table (over 27 million lines and growing) with inventory and their related cost transactions. To get a inventory (or cost ) on hand balance I do the standard formula

Balance Qty = CALCULATE(SUM('Item Value New'[Item Ledger Entry Quantity]),FILTER(ALL(Dates),Dates[Date]<=MAX(Dates[Date])))

Which works great. However, Now what I want to do is get an average of the inventory balances on hand over a date range. That way for items that are produced less often we still get a good idea of the carrying quantity and value of inventory.

To do so, I first tried creating a new table with summarize columns that would have the balance on hand for each date, and then taking an average of that balance

Inv Summary Col = SUMMARIZECOLUMNS('Item Value New'[Item Ledger Entry Quantity], 'Item Value New'[Posting Date],


"Bal Qty", CALCULATE(SUM('Item Value New'[Item Ledger Entry Quantity]), FILTER(ALL('Item Value New'[Posting Date]), 'Item Value New'[Posting Date]<=MAX('Item Value New'[Posting Date] ) ) ),


"Bal Cost", CALCULATE(SUM('Item Value New'[Cost Amount (Actual)]) , FILTER(ALL('Item Value New'[Posting Date]) , 'Item Value New'[Posting Date] <= MAX('Item Value New'[Posting Date] ) ) )


)

Then I do an average with something like
Average(Bal Qty)

However, while that worked fine in Power BI desktop, every time I tried to refresh from the Power BI service I got a memory allocation error after the refresh timed out at around two hours.

So now I tried an alternate method. Basically just two measures

Balance Qty = CALCULATE(SUM('Item Value New'[Item Ledger Entry Quantity]),FILTER(ALL(Dates),Dates[Date]<=MAX(Dates[Date])))

followed by

Avg Bal Qty = AVERAGEX(VALUES(Dates[Date]),[Balance Qty])

Which seems like it’s doing the job, but when I update the matrix (say expand the product hierarchy, or switch to a different product category), things are going REALLY slow.

So is there a better way to approach this?

Thanks,

5 REPLIES 5
Anonymous
Not applicable

can you push it into a SQL stored procedure and populate some flat tables with summary data? Or build cubes elsewhere?

Anonymous
Not applicable

I have no idea.  I've never done that before.

Anonymous
Not applicable

Ok. Well my guess is you are pushing the limits of what is possible with a non-premium workspace. That said, using "AverageX" will be far more costly than "Average" because it has to calculate it for each row in the dataset. 

The challenge with a 27 million row import held in memory of the powerBI database is that it is going to be massive. That is why I suggested aggregation outside of PowerBI, first.

Anonymous
Not applicable

"That said, using "AverageX" will be far more costly than "Average" because it has to calculate it for each row in the dataset. "

 

My understanding is this is not true.  And that Average and AverageX are progamatically the same, and that average is actually translated to Average X at run time.   See "The definitive guide to Dax".

 

 

Anonymous
Not applicable

@Anonymous: I'll take a look, thank you for sharing. I'm sorry I don't have a solution for you.

 

FWIW it's worth my assumption is that it worked like SQL, where calculating at the row level and then rolling it up is indeed more calculations, and thusly, more expensive.

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.