Performance issue with 20k dimension rows

[Daniel29195]

the problem is as follow : 

i have a dimension table (20000 rows ) dimaccounts   and a fact table (  2 million rows )  fact_sales

im building a pareto chart : 

 my DAX measure : 

DEFINE

MEASURE 'fact'[cumu] = 

var _current_sales =[sales]
var ds = 
	filter(
		ADDCOLUMNS(
			ALLSELECTED('dim'[Value]) , 
			"@sales" , sum('fact'[Value])
		) ,
		[@sales] >= _current_sales
	)

var res =  sumx (ds  ,[@sales] ) 


return res  

[sales] measure is a simple sum (fact_sales[amount]) 

my approaches : 

1.  visual calculation --  performance issue. 

2.  rankx in code --  performance issue 

3.  window function --  huge performance issue. 

explanation of the measure : 

it is calculating the cumulative sum of sales, ordered by accountid .

so if i have per example : 

accountid         sales

1                           20

2                           40

3                            10

the visual should show  the cumulative as below : 

accountid            sales    cumulative

2                            40            40

1                             20           60

3                             10            70

the problem is that the dax measure is taking like 50 seconds to finish : 

and im stuck into finding another way to caluclate the value that can improve performance. 

Any help on this would be appreciated. 

Note : even with a fact table of 20000 rows will take a long time to finish exeucting the dax code. 

[sio2Graphs]

Hello,

I have used the provided data to create the attached using "Pareto by sio2Graphs", you can also use "Pareto+”, to quickly construct pareto charts without needing to use measures or DAX.

It looks a bit strange, because there is no real variation in the sums of the sample dataset.

sio2Graphs

sio2graphs on AppSource 

[Daniel29195]

@lbendlin 

and this is a file with sample data with real tables not calculated tables : 

https://drive.google.com/file/d/1sj_GoJZi4uh66mPDQcN3jH4UgvbmwP4C/view?usp=sharing

[lbendlin]

Post a sample PBIX.

[Daniel29195]

@lbendlin 

dimtablee :

generateseries(1,20000)

facttable:

fact =

fact =

GENERATE(SELECTCOLUMNS(GENERATESERIES(1,20000),"dimid",[Value]) , GENERATESERIES(1,200))

link :  

the file im testing on contains those 2 tables  created like this. 

[Daniel29195]

Please keep in mind that in the real data , the calculation is affected by date . 

so i cant go with static calculation approaches 

[lbendlin]

You mention Pareto chart.  You have too many dimension values for that. The Column chart will cut off your data and do forced grouping, singnified by the blue "i"  in the top right corner. 

Find another dimension to use for the Pareto.

Note: as a super user you can attach files here directly.