Hello Experts,
We are working on a requirement that is related to Pareto Analysis wherein the pareto key used needs to act as filter in another table view. It is continuation of https://community.powerbi.com/t5/Desktop/pareto-calculations/m-p/2918459/thread-id/1001997. While we are successfuly in implementing the pareto analysis, facing challenges to implement the 2nd visual.
Using example data have explained below .
Assume there is one table with below contents which we are using in the model ( Showing a single table while in actual model,data is split between dimensions and facts with star schema design in the model - Item dimension, Country Dimension, Calendar dimension, Customer item dimension, Sales Fact etc)
Item # | Product Family | Dept | Region | Country | Cust # | Cust Name | Sales $ |
Item-1 | Base | Auto | EMEA | Spain | 5000184 | John.S | $ 173,342.63 |
Item-1 | Base | Auto | APAC | China | 5000184 | John.S | $ 1,488.21 |
Item-2 | Food | Auto | EMEA | Spain | 5000184 | John.S | $ 199,256.91 |
Item-2 | Food | Auto | APAC | China | 5000184 | John.S | $ 23,668.25 |
Item-3 | Base | Auto | EMEA | Spain | 5000184 | John.S | $ 145,173.84 |
Item-3 | Base | Auto | APAC | Japan | 5000184 | John.S | $ 8,956.46 |
Item-4 | Raw Material | Auto | EMEA | Spain | 5000184 | John.S | $ 563,051.28 |
Item-5 | Base | Auto | EMEA | Spain | 5000184 | John.S | $ 307,612.43 |
Item-6 | Raw Material | Auto | EMEA | Spain | 5000184 | John.S | $ 137,860.39 |
Item-7 | Raw Material | Auto | EMEA | Spain | 5000184 | John.S | $ 109,219.93 |
Item-8 | Base | Auto | EMEA | Spain | 5000184 | John.S | $ 215,791.29 |
Item-9 | Base | Auto | EMEA | Spain | 5000184 | John.S | $ 156,544.56 |
Item-10 | Raw Material | Auto | EMEA | Spain | 5000184 | John.S | $ 186,542.19 |
Item-11 | Beverage | Others | EMEA | Germany | 5002344 | Betty.C | $ 324,736.95 |
Item-12 | Textile | Auto | EMEA | Spain | 5000184 | John.S | $ 287,203.67 |
Item-12 | Textile | Auto | APAC | China | 5000184 | John.S | $ 53,232.15 |
Requirement is to show below Table Visuals in Power BI
Visual 1 :
Show the Products that contributed to the Top 50% of the overall global Sales ( Pareto Analysis) for the entire financial year ..
I.e. show only the items from below dataset where running total of sum total sales of the item <= “50% of Total sales”
Item # | Total Sales $ | Running Total | 50% of Total Sales |
Item-4 | $ 563,051.28 | $ 563,051.28 | 1,446,840.57 |
Item-12 | $ 340,435.81 | $ 903,487.10 | 1,446,840.57 |
Item-11 | $ 324,736.95 | $ 1,228,224.05 | 1,446,840.57 |
Item-5 | $ 307,612.43 | $ 1,535,836.48 | 1,446,840.57 |
Item-2 | $ 222,925.16 | $ 1,758,761.64 | 1,446,840.57 |
Item-8 | $ 215,791.29 | $ 1,974,552.93 | 1,446,840.57 |
Item-10 | $ 186,542.19 | $ 2,161,095.12 | 1,446,840.57 |
Item-1 | $ 174,830.84 | $ 2,335,925.96 | 1,446,840.57 |
Item-9 | $ 156,544.56 | $ 2,492,470.52 | 1,446,840.57 |
Item-3 | $ 154,130.31 | $ 2,646,600.83 | 1,446,840.57 |
Item-6 | $ 137,860.39 | $ 2,784,461.22 | 1,446,840.57 |
Item-7 | $ 109,219.93 | $ 2,893,681.14 | 1,446,840.57 |
Visual will show below that shows 3 distinct items that contributed to 50% of sales. Note that Item-12 falls into the 50% bucket because of the combined sales from both the countries. If sales is considered individually at country level, this item would not have entered the top 50% bucket
Item # | Pareto Sales $ |
Item-4 | $ 563,051.28 |
Item-12 | $ 340,435.81 |
Item-11 | $24,736.95 |
TOTAL | $1,228,224.05 |
We were able to achieve this Visual using the below DAX measures used for Pareto Analysis but facing challenges to achieve the 2nd visual as this visual needs the “Item” list from Visual 1 and then aggregate the Total Sales of those items grouped by “Country” (and similar attributes).
DAX measures used for the Pareto Analysis are below
InvoiceSales = SUM('Fact-Sales'[INVOICE_SALES])
RunningTotal =
VAR ItemGrossSales = [InvoiceSales]
RETURN
SUMX(FILTER(SUMMARIZE(ALLSELECTED('Fact-Sales'),'Fact-Sales'[ITEM_NUMBER],"TotalGrossSales",[InvoiceSales]),[TotalGrossSales]>=ItemGrossSales),[TotalGrossSales])
MaxParetoSales =
VAR TotalParetoGrossSales = CALCULATE([InvoiceSales],ALLSELECTED('Fact-Sales'))
RETURN
TotalParetoGrossSales * 0.5
ParetoSales = SUMX(VALUES('Fact-Sales'[ITEM_NUMBER]),IF([RunningTotal]<= [MaxParetoSales],'Fact-Sales'[InvoiceSales],0))
ParetoItemCount =
CALCULATE (DISTINCTCOUNT('Fact-Sales'[ITEM_NUMBER]), FILTER(VALUES('Fact-Sales'[ITEM_NUMBER]),
[RunningTotal]<= [MaxParetoSales]))
Visual 2: Show the Countries of the sales of the items that are contributing to the 50% Sales and distinct count of items
Country | Total Sales $ | Item Count |
Spain | $ 850,254.95 | 1 |
China | $ 53,232.15 | 1 |
Germany | $ 324,736.95 | 1 |
TOTAL | $ 1,228,224.05 | 3 |
Like visual2, we need more visuals wherein instead of country, it can be "Region" or "Customer" and so on..Basicallly aggregate the item sales grouped by related attributes.. Do we have any way to achieve this?
