Forum Discussion
Count Distinct Cumulative for Pareto
I have table like below:
| Year | Month | Region | Product | Item | Value |
| 2025 | 1 | Asia | Product A | Item A1 | 100 |
| 2025 | 1 | Asia | Product A | Item A2 | 100 |
| 2025 | 1 | Asia | Product A | Item A3 | 200 |
| 2025 | 1 | Asia | Product B | Item B1 | 100 |
| 2025 | 1 | Asia | Product B | Item B2 | 200 |
| 2025 | 1 | Europe | Product A | Item A1 | 200 |
| 2025 | 1 | Europe | Product A | Item A2 | 220 |
| 2025 | 1 | Europe | Product A | Item A3 | 240 |
| 2025 | 1 | Europe | Product A | Item A4 | 260 |
| 2025 | 1 | Europe | Product A | Item A5 | 280 |
| 2025 | 1 | Europe | Product B | Item B3 | 300 |
| 2025 | 1 | America | Product B | Item B1 | 320 |
| 2025 | 1 | America | Product B | Item B4 | 340 |
| 2025 | 1 | America | Product B | Item B3 | 360 |
Which i want to count distinct items each region montly, and want to create pareto chart for viz. and i want to get table like below:
| Filter | Month | 1 | ||
| Region | Count Item | Total Item | Pct | **bleep**. Pct |
| Asia | 5 | 14 | 35.71% | 36% |
| Europe | 6 | 14 | 42.86% | 79% |
| America | 3 | 14 | 21.43% | 100% |
Can you help me with the dax for this case count distinct and cumulative sum
8 Replies
Wasn't quite sure how you were wanting to define your SalesCount so it might need a tiny tweak.
I used:Sales Count = COUNTROWS(Sales)and:
Pareto % = IF( ISINSCOPE( Sales[Region]), VAR __AllSelectedReg = ALLSELECTED( Sales[Region] ) VAR __RegTable = ADDCOLUMNS( __AllSelectedReg, "@Count", [Sales Count]) VAR __CurrentCount = [Sales Count] VAR __CumSalesTable = FILTER(__RegTable, [@Count] >= __CurrentCount) VAR __CumSales = SUMX( __CumSalesTable, [@Count]) VAR __AllSelectedSalesCount = CALCULATE( [Sales Count] , __AllSelectedReg) VAR __Result = DIVIDE(__CumSales, __AllSelectedSalesCount) RETURN __Result)and then a standard Line and Stacked Column Chart with Region on the X-axis and Pareto % on the Line y-axis
Your dax quite good but still not solved, since the table that i showed before is masked. my dax to distinct count is like below :
Count = CALCULATE(DISTINCTCOUNT(table1[item]),FILTER('table1','table1'[value] <> 0),FILTER('table1',NOT('table1'[market] = "abc" && 'table1'[Product] = "Product B")))
so i got miss result like this
Region Count Pareto
Eropa 124 0.84
Asia 18 0.96America 5 0.99Africa 3 1.01
total pareto is over 100 percent and the calculation pct sligthly wrong.
i think we should change this part, cuz my count using some kind of filtersVAR __AllSelectedSalesCount = CALCULATE( [Sales Count] , __AllSelectedReg)
is the issue not how you are using distinct count?
the Distinct part is only checking for distinctness within the current row context (for example the current region) but when you are looking at a cumulative value there the context is across all regions.
See below where I redfined salescount using distinctcount, particularly notice that the total for sales count is 10 despite the individual values when summed being higher than 10
Hi,
I have solved a similar question in the attached files.
Hi,
Here is an alternate solution using certified, custom visual "Pareto by sio2Graphs".
*make sure data does not include blanks when using below formula. Percent = FORMAT(DIVIDE(COUNT(Sheet1[Item]),COUNTROWS(ALLSELECTED('Sheet1')),1),"Percent")cynthia@sio2Graphs.com
easy, effective, aesthetic
try Pareto+ for advanced features
Hi Anonymous ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you!!
Hi Anonymous ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you!!
Hi Anonymous ,
I hope the information provided is helpful. Feel free to reach out if you have any further questions or would like to discuss this in more detail. If responses provided answers your question, please accept it as a solution so other community members with similar problems can find a solution faster.
Thank you!!
