I’m having trouble with something that seems like it should be simple. I have a fact table with both Sales and Forecasts in it at product/store/date level. While presenting the product and aggregated forecast filtered by product/date, I would also like to present the minimum total sales value for a given date for that product across all dates that have sales populated.
I’ve attached a .pbix and sample data with an objective tab that contains additional details and expected results.
I agree with you in that this SEEMS like it should be simple, but in fact it takes some fairly hefty virtual table work to get the right result, since you need to sum sales over the full range of dates and products, and then take the minimum total sales from that virtual table, subject to the filter conditions created by the slicers.
Here’s the measure that does the heavy lifting:
Min Total Sales =
VAR SeLDate = SELECTEDVALUE( dimDate[Date] )
VAR SelProd = SELECTEDVALUE( DimProduct[Product] )
VAR vTable =
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(
FactSalesFcst,
FactSalesFcst[Product],
FactSalesFcst[Date]
),
"@TotSales", [TotalSales]
),
FILTER(
ALL( FactSalesFcst ),
FactSalesFcst[Product] = SelProd &&
FactSalesFcst[Date] <= SeLDate
)
)
VAR Result =
CALCULATE(
MINX( vTable, [@TotSales] ),
FILTER(
vTable,
FactSalesFcst[Product] = SelProd &&
FactSalesFcst[Date] <= SeLDate
)
)
RETURN Result
And here’s what it looks like put together:
I hope this is helpful. Full solution file posted below.
Thanks Brian. Measure and explanation makes sense. This works within context of the sample data, but the actual fact table that I’m working against contains roughly ~250M rows.
Even when applying slicer filter for single product, I’m receiving a “Couldn’t load data for this visual - Not enough memory to complete operation” error. Would you anticipate encountering this issue considering the size of the dataset?
What are the specs of the machine you’re running it on? To be honest, I’ve never run a dataset that big. Any chance you could provide me a copy of the file and I could see if/how it runs on my machine, which is a pretty hefty workstation with a lot of memory?
Intel Core i5-6500 @ 3.2 GHz, 16 GB RAM. It’s a 2.5 GB .pbix, lol. Should prob be connecting to fact using direct query but toying with import for better report performance.
I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!
Hi @_mk7, did the response provided by the Experts help solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Thanks for the suggestions! Both options are returning the min total sales quickly. Taking a look at performance analyzer and comparing the two queries, it appears that “Min Total Sales 3” is returning the quickest.
The measure you provided a while back works great, however, we’re running into volume that’s causing us to hit premium capacity per-query memory limits (P1 SKU, default limit equates to roughly 6144MB).
More details: Resource Governing: This query uses more memory than the configured limit. The query — or calculations referenced by it — might be too memory-intensive to run. To run this query, you could simplify the query or its calculations, or reach out to your capacity administrator to see if they can increase the per-query memory limit. More details: consumed memory 6679 MB, memory limit 6144 MB. Learn more, see https://go.microsoft.com/fwlink/?linkid=2159752.
Any ideas how to make this measure less resource intensive? I don’t think we want to adjust the query memory limit in this situation. Regarding the data set, there are roughly 40K products and 1K stores. Fact table contains roughly 300M rows.
