Hi all ,
Dataset : Transaction dataset of sales across productnumber(each product can have multiple line items)
Requirement:
Generate Pareto aginst Count of unique products
X axis = 1 to Count (unique product number) -- 1 means the one with highest sales and last item with least sales
Y axis = Cumulative Sales divide by total sales
Since both are measure , we have option of making scatter plot to make this Pareto chart
Measure i have created those have generated correct result however they are too slow . I have data 18 lakh rows and 12 thousand unique product numbers.
Rank_All_Productnum_Sales = RANKX(ALL('Data'[ProductNumber]),[Total Sales])
Pareto_Product_num_Sales = SUMX(TOPN(RANKX(ALL('Data'[ProductNumber]),[Total Sales]),all('Data'[ProductNumber]), [Total Sales]), [Total Sales])
Pareto_Product_num_Sales= CALCULATE(sum('Data'[SALES]),TOPN([Rank_All_Productnum_Sales],all('Data'[ProductNumber]),[Total Sales]))
Pareto_Product_num_Sales =
var Rnk_ = rankx(all('Data'[ProductNumber]),[Total Sales])
Return
CALCULATE([Total Sales],filter(all('Data'[ProductNumber]),rankx(all('Data'[ProductNumber]),[Total Sales]) >= rnk_))
Pareto_Product_num_Sales =
Var rank_ = rankx(all('Data'[ProductNumber]),[Total Sales])
Return
sumx(filter(all('Data'[ProductNumber]),rankx(all('Data'[ProductNumber]),[Total Sales]) >= rank_),[Total Sales])
* We need dynamically functioning chart (slicer on the page should work on this). Summary table not working
* Some product number can have same rank. Create a table for rank and then creating a data across this wont work too.
Please help us with optimizing the Dax queries , or suggest any other way to work with this
