Forum Discussion
Incorrect grand total and performance issue
First I use the following measure
3 M avg Switch =
var cost = SUM ( DimCost[Actual Price] ) *[3 M avg RD QTY]
RETURN
IF (
[Selected view] = 1,
[3 M avg RD QTY],
ROUND (cost, 0 )
)
In a table with DImDistritbutor[Distributor] and DimItem[Product] which gave me the wrong grand total. So then I used the following measure
3 M avg = SUMX(CROSSJOIN(VALUES(DimDistributor),VALUES(DimItem)),[3 M avg Switch])
Now I get the correct grand total, but It heavily affected the performance. There are other measures also similar to this one and now it takes more than 15 seconds to load the table. Further when it published "visual has exceeded the available resources power bi" error occurs.
3 M avg RD QTY =
CALCULATE (
AVERAGEX ( VALUES ( DimDate[Month] ), [Total RD QTY]),
DATESINPERIOD (
DimDate[Date],
EOMONTH(TODAY(),-1),
-3,
MONTH
)
)
Please give me a solution to get both correct grand total and performance.
2 Replies
- amitchandak
Super User
Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
what are calculation for [Selected view], [3 M avg RD QTY]
- AnonymousNot applicable
amitchandak
I already mentioned 3 M avg RD QTY, which is3 M avg RD QTY = CALCULATE ( AVERAGEX ( VALUES ( DimDate[Month] ), [Total RD QTY]), DATESINPERIOD ( DimDate[Date], EOMONTH(TODAY(),-1), -3, MONTH ) )Selected view = SELECTEDVALUE('Stock View'[Code],1)