Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

  • 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