Forum Discussion
matrix lowest level (rows) taking forever?
I have the below measure where I added the IF clause to avoid negative %:
GM %:=IF( NOT [Net Revenue] < 0
,DIVIDE( [Gross Margin] ,[Net Revenue])
)
Each respective measure used:
Gross Margin:=
CALCULATE(
SUM(Revenue[GrossMargin]),
FILTER('Center', NOT 'Center'[Department]in {BLANK()})
)
Net Revenue:=
CALCULATE(
SUM(Revenue[NetRevenue]),
FILTER(‘Center', NOT 'Center'[Department]in {BLANK()})
)
(the filter in these last 2 measures is used to avoid in the visuals the 'blank' group in the visuals)
Before I added the IF( NOT [Net Revenue] < 0 it worked fine, but now the report is very very slow, specifically:
I am using a Matrix, and in rows I have 4 “levels”… The report works fine on the first row/level, if I drill down, the second level takes a couple of seconds, the 3rd level takes several minutes, the 4th level times out (never displays).
In values section I have 5 different measures.
In the beginning I thought it was that the rows/Drill down was too deep… But by playing around I realize that by removing GM% I can drill down to the lowest level instantly (no wait!) !!
How can I troubleshoot this? I can fix it by either removing GM% measure or if keep it, but removing the IF it also works fine…
Is there any other way my IF would work? Or shall I do anything in the rows/drill down part??
1 Reply
- amitchandak
Super User
Anonymous , Try measures like
Gross Margin:=
CALCULATE(
SUM(Revenue[GrossMargin]),
FILTER('Center', NOT(isblank( 'Center'[Department]))
))Net Revenue:=
CALCULATE(
SUM(Revenue[NetRevenue]),
FILTER('Center', NOT(isblank( 'Center'[Department]))
))GM %:=IF( [Net Revenue] > 0
,DIVIDE( [Gross Margin] ,[Net Revenue]) , blank()
)Unless you want Net Revenue > 0, do not use if. divide is good enough to handle 0