matrix lowest level (rows) taking forever?

[Anonymous]

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??

[amitchandak]

@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