Pareto chart jagged because of duplicate values

[Anonymous]

Hello,

I appreciate any help in advance on this. I am trying to create a Pareto chart that can dynamically filter. I have created a Pareto chart using the formula below but it creates some jagged parts in the line because some categories have the same exact counts. I am using a summarized table based on the underlying dataset that way you can dynamically filter (if there is a better option I am all ears). 

How do I handle the exact values for the pareto? In excel you would calculate a running total after you have sorted the values from high to low then you can create the cumulative percentage. The only thing I could think of is maybe making an index column using dax but I have hit a road block there since there is no reference column and you would have to force the sort some how in the summarized table.

var  totalComplaints = CALCULATE(COUNT(CUSTOMER_FEEDBACK[FEEDBACK#]),ALLSELECTED(CUSTOMER_FEEDBACK))

var currentComplaints = COUNT(CUSTOMER_FEEDBACK[FEEDBACK#])

var summarizedTable = 
SUMMARIZE(
    ALLSELECTED(CUSTOMER_FEEDBACK),
    CUSTOMER_FEEDBACK[DEFECTCODE],
    "Complaint Count", COUNT(CUSTOMER_FEEDBACK[FEEDBACK#])
)

var cumulativeSum = 
SUMX(
    FILTER(summarizedTable, [Complaint Count] >= currentComplaints),
    [Complaint Count]
)

return

cumulativeSum/totalComplaints

This is the result of the summarized table. Each Category is a single row in the underlying table.

Category	Count
Identification Numbers	126
Does Not Meet Requirement	105
Other	79
Length	70
Thread Form	68
ID	58
Burr/Chip	56
Scratches/Dents	43
Missing Information	39
Hex/Square	36
Missing	30
OD/Major Diameter	30
Minor Diameter	29
Profile	27
Quantity	27

[V-lianl-msft]

Hi @Anonymous ,

Any chance you can share a PBIX file with dummy data?(cloud service like onedrive for business)

Please mask any sensitive data before uploading.

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

[aaaaaaaaaaaaaa]

Hi Liang,

I 'm having the exact same issue, when no external filters are applied everything works

then when I filter dates or any other column, some random duplicates appear

Here is the DAX measure used:

Pareto Sales % = 
    VAR total_rev = 
        CALCULATE(
            SUM( sales[revenue_after_commission] ),
            ALLSELECTED( sales )
        )

    VAR grouped_table = 
        FILTER(
            SUMMARIZE(
                ALLSELECTED( sales ),
                products[uid],
                "grouped_revenue", 
                SUM( sales[revenue_after_commission] )
	        ),
            [grouped_revenue] > 0
        )

    VAR cummu_rev =
        SUMX(
            FILTER( grouped_table, SUM( sales[revenue_after_commission] ) <= [grouped_revenue] ) , 
            [grouped_revenue]
        )
    
    RETURN
        DIVIDE( cummu_rev, total_rev )

It's like ALLSELECTED is glitching out, is this a bug?

Please advise, thank you!

[aaaaaaaaaaaaaa]

Update:

Publishing the report online creates random duplicates even though no filters are selected.

After playing around in DAX studio, I found out grouped_table and SUMMURAZE results are actually consistant.
However, SUMXing an expression table gives different result, displacing duplicates randomly every computation, which is kinf of scary tbh

The issue is reproductible on any fact table.

Is this a normal behaviour?

Any help would be much appreciated, thank you!

[aj1973]

Hi @aaaaaaaaaaaaaa , @Anonymous 

Can you guys share a sample of your file?

[aaaaaaaaaaaaaa]

Hi Amine, 

Thank you for your reply

The source of the issue was that SUMX is not able to iterate over duplicate values for 2 different categories, each category must have unique sales figures for the cumulative sum to work...

I found a weird way to make SUMX cumulative calculation work on duplicate values by substracting the category ID divieded by 1000 to the values so all values (sales) become unique:

Pareto Sales % = 
    VAR total_rev = 
        CALCULATE(
            SUM( sales[revenue_after_commission] ),
            ALLSELECTED( sales )
        )

    VAR grouped_table = 
        FILTER(
            SUMMARIZE(
                ALLSELECTED( sales ),
                products[uid],
                "grouped_revenue", 
                SUM( sales[revenue_after_commission] )
	        ),
            [grouped_revenue] > 0
        )

    VAR cummu_rev =
        SUMX(
            FILTER( grouped_table, SUM( sales[revenue_after_commission] ) - MAX( products[uid] ) / 1000 <= [grouped_revenue] ), 
            [grouped_revenue]
        )
    
    RETURN
        DIVIDE( cummu_rev, total_rev )

where products and sales tables are linked as follows:

It is not pretty but it works!

I tried to upload a sample pbix to illustrate the problem but it seems this file type is not supported

Thanks again

[aj1973]

Hi @aaaaaaaaaaaaaa 

Please use an online service to upload your file, like Onedrive, Google dirve,.... and share the link