Pareto chart with duplicate values

[marco_2020]

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

[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.

[v-yingjl]

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/38490

Best Regards,
Yingjie Li

If 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 Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[marco_2020]

Hello @v-yingjl,

thank you for your precious answer, but I still miss one thing: I need to dynamically create the "sort column" field since the data table can be modified and then the Pareto chart report has to update accordingly.

Is there a way to calculate the column which you have added to the original table?

Many thanks.

[v-yingjl]

Hi @marco_2020 ,

I'm afraid not. Because the logic of sory by A,C,B is difficult to define. Although the count of A and C is both 6, but the unicode of them is C > B > A so cannot define the logic to create a sort column dynamically. Although it is more cumbersome to force define the sorting column manually, it is a more direct and effective method.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@marco_2020 ,

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_2020]

@amitchandak  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.

[marco_2020]

@amitchandak  I have tried all combinations using 3 dots.. I have obtained this:

But in this way, the cumulative perc is not increasing.

Do you have any ideas to solve this problem?

Thanks