Forum Discussion
Helper IPareto chart with duplicate values
Hello,
I am working on a dataset as the one reported in the following: I have a category field (with 3 possible values: A,B,C). As you can see In total I have 6 record with A and C and 4 with B. I would like to create a Pareto chart with this kind of data, where it is possible to have duplicate values, but I am having the chart reported in the following where I have the same percentage values for bins with the same count. I also report the formulas.
Total = CALCULATE(COUNT(Sheet1[Index]),ALL(Sheet1))
CumulativeCount =
var totalTest = COUNT(Sheet1[Index])
RETURN
SUMX(FILTER(
SUMMARIZE(ALLSELECTED(Sheet1),Sheet1[Category],"NumberOfRecords",[NumOfRecords]),
[NumberOfRecords] >= totalTest),[NumberOfRecords])
CumulativePerc = [CumulativeCount]/[Total]
Thank you.
Marco
11 Replies
- amitchandak
Super User
cumm = calculate(COUNT(Sheet1[Index]), filter(allselected(Sheet1),Sheet1[Index] <=max(Sheet1[Index]))) total = calculate(COUNT(Sheet1[Index]), allselected(Sheet1)) Cumm % = divide([cumm],[total])
marco_2020amitchandak thanks for the answer.
With your formula I obtain a chart which is not ordered in the Pareto way, as shown in the following:
I would like to have A and C column as first ones. Is it possible?
Thanks,
Marco
- amitchandak
marco_2020 , Using the three dots on the visual see if you can sort descending on the Measure used in bar.
- v-yingjl
Community Support
Hi marco_2020 ,
In your posted formula, not sure what does [NumOfRecords] represent so that could not reproduce it well in my environment. Could you please considering sharing the information about it or a sample .pbix file and expected result for further discussion?
Sample file and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
- marco_2020
Hi v-yingjl,
I attach in the following all my measures:
NumOfRecords = COUNT(Sheet1[Index]) -------- total = CALCULATE(COUNT(Sheet1[Index]),ALL(Sheet1)) --------- CumulativeCount = var totalTest = COUNT(Sheet1[Index]) RETURN SUMX(FILTER( SUMMARIZE(ALLSELECTED(Sheet1),Sheet1[Category],"NumberOfRecords",[NumOfRecords]), [NumberOfRecords] >= totalTest),[NumberOfRecords]) --------- Cumm % = divide([CumulativeCount],[total])Sheet1 is the following table:
Index Category 1 A 2 B 3 B 4 C 5 C 6 C 7 C 8 A 9 A 10 B 11 A 12 A 13 A 14 B 15 C 16 C Following there is the chart which I am obtaining with current formulas and in red the one which I would like to obtain. In particular the problema regards "A" and "C" categories for which I currently have the same percentage value (as if these 2 categories are considered the same thing).
Have you ideas about how to help me?
Thanks in advance.
- v-yingjl
Hi marco_2020 ,
If you want to achieve the same goal as the Pareto chart in the combo chart, you need to create a sort column manually in your data source to force the definition order because combo chart cannot automatically define the order of an A,C,B based on the current data.
Table will be like this:
Create this measure:
Measure = CALCULATE( COUNTROWS('Table'), FILTER( ALL('Table'), 'Table'[sort column] <= MAX('Table'[sort column]) ) ) Cumm % = DIVIDE([Measure],[total])Attached sample file that hopes to help you: Pareto chart.pbix
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
- v-yingjl
Hi marco_2020 ,
If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly. Thanks!
Best Regards,
Yingjie Li- marco_2020
Hi, I have not solved my issue yet. Sorry. Thanks again.
