Sumx and Summerize is performing too slow in Matrix

[salmashaikh]

Hello everyone i have created Matirx report which Looks like Below :

All the columns  are measures with fixed value 0,50 ,100 ... I am maitainng RAG status . where 0 is red 50:Amber and 100 :green 

My total is : Total of Column / RAG  / Sites(Business Hirachy)

I am using SUMX with Summarization in My DAX which is as below : 

%ADH_RiskStatus =

VAR sumtbl =SUMMARIZE('Sites & Facilities','Sites & Facilities'[Document.SiteName] ,"RISK1",[ADH_RiskStatus])

VAR TotalRiskbySite= (SUMX(sumtbl,[RISK1]))

RETURN

(TotalRiskbySite ) /[ADH_SiteCount]

This logic is working very fine . but it is very slow in performance . I need help/suggestion to improve perfromance .

[V-lianl-msft]

Hi @salmashaikh ,

As SUMX is a iterator function, it is degrading the performance.

https://www.kasperonbi.com/tune-your-powerpivot-dax-query-dont-use-the-entire-table-in-a-filter-and-... 

https://powerpivotpro.com/2015/08/nested-sumx-or-dax-query/ 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.