Forum Discussion
Count Distinct Cumulative for Pareto
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