Pareto chart with tie values

[Anonymous]

Hello Community  -  

I have a data set where I am tracking the number of late lines, per product.   And doing a pareto of these items.   A number of items have the same amount of late lines.   Meaning, the order line the product was on shipped late.    So for example, in the chart below you can see that a number of different items have the same amount of late shipped lines...resulting in a flat line where the ties occur.     I guess my question is whether or not this is acceptable in the world of pareto charts?    Or should a pareto Always have the traditional curve to it?  

The conditional formatting (grey color) kicks in at the 80% value on the pareto blue line.  

[Syndicate_Admin]

I was able to reproduce your solution, but the data zeroed out (which has values >1 in other months) is showing up on the graph. I use filters for different months and periods (quarter and semester) to generate periodic reports, and I intend to use this same base. How can I hide them?

[Fowmy]

@Anonymous 

Ideally, in a Pareto chart, each value should get added and that should be calculated as a % of the total. I think you need to look at your calculation to avoid getting similar percentages when they the same.

[Anonymous]

@Fowmy     Makes sense, but where you have repeated values, what I am getting is the same cumulative percent.   For example.   So you are saying I should have a different cumulative percent even if I have the same value for the late shipped lines?

# of late lines      cumulative percent 

6                              33.85%

6                              33.85%

6                              33.85%

7                              28.69%

7                              28.69%

[Fowmy]

@Anonymous 

When it comes to ties, you need to incorporate another column to break them, I did a sample file with Pareto using this technique. Please find attached the file below my signature. Product B and C have the same values but still adds up.

Pareto =
VAR __CurrentRank =
    RANKX (
        ALL ( Table1[Product] ),
        CALCULATE ( [Total Sales] + UNICODE ( MIN ( Table1[Product] ) ) / 1000 ), , ,
        DENSE
    )
VAR __Total =
    CALCULATE ( [Total Sales], ALLSELECTED () )
VAR __Cummulative =
    SUMX (
        FILTER (
            ADDCOLUMNS (
                ALLSELECTED ( Table1[Product] ),
                "rank",
                    RANKX (
                        ALL ( Table1[Product] ),
                        CALCULATE ( [Total Sales] + UNICODE ( MIN ( Table1[Product] ) ) / 1000 ), ,,
                        DENSE
                    )
            ),
            [rank] <= __CurrentRank
        ),
        [Total Sales]
    )
RETURN
    DIVIDE ( __Cummulative, __Total )

[Anonymous]

I would just add that it seems wherever there is a tie, the pareto line dips down.   As seen here: 

[Anonymous]

Hi @Fowmy    I applied your measure, and my results are a bit sporadic.    Thank you for taking the time to help...just curious why I might be getting this erratic behavior.   Here is the measure I used.   Basically, your measure with my actual names included.  

Pareto =

VAR __CurrentRank =

RANKX (

ALL ( ShippedOrdersALL_Query[Item] ),

CALCULATE ( [Quick Ship Late Shipped Lines] + UNICODE ( MIN ( ShippedOrdersALL_Query[Item] ) ) / 1000 ), , ,

DENSE

)

VAR __Total =

CALCULATE ( [Quick Ship Late Shipped Lines], ALLSELECTED () )

VAR __Cummulative =

SUMX (

FILTER (

ADDCOLUMNS (

ALLSELECTED ( ShippedOrdersALL_Query[Item] ),

"rank",

RANKX (

ALL ( ShippedOrdersALL_Query[Item] ),

CALCULATE ( [Quick Ship Late Shipped Lines] + UNICODE ( MIN ( ShippedOrdersALL_Query[Item] ) ) / 1000 ), ,,

DENSE

)

),

[rank] <= __CurrentRank

),

[Quick Ship Late Shipped Lines]

)

RETURN

DIVIDE ( __Cummulative, __Total )

[Fowmy]

@Anonymous 

Can you share a PBIX file with the above chart and sample data? You can replace actual numbers with dummy numbers. 

Save the file in OneDrive or any other location and share the link.

[Anonymous]

Hi @Fowmy     I am not at liberty to share the file, but I think I've at least discovered a pattern around where the pareto line is dipping down.   I see that you are using Unicode in the measure and it is interesting that the "dips" on the line happen everywhere there is a similarity in the part number.   For example, the "44" below is a three way tie.   The first digit on the first "44" is a 7.    However, on the other two the part numbers both start with 713...and you can see the percentage is the same for both.    Likewise for the 43.   Both part numbers in this case begin with "C".     The measure works fine for ties where the part numbers do not have these similarities.    Perhaps changing the "unicode" to something different would help?   Just not sure how to go about this.  

[Fowmy]

@Anonymous 

I took just one letter in my product column that is the reason. I modified the measure and a new measure to rank the products:

ProductRank = 
VAR __currentproduct =
    SELECTEDVALUE ( Table1[Product] )
RETURN
    DIVIDE (
        COUNTROWS (
            FILTER ( ALLSELECTED( Table1[Product] ), Table1[Product] <= __currentproduct )
        ),
        100
    )

Pareto Measure:

Pareto = 
VAR __totalsales =
    CALCULATE ( [Total Sales], ALLSELECTED () )
VAR __Curretnrank =
    RANKX ( ALL ( Table1[Product] ), [Total Sales] + [ProductRank],, DESC )
RETURN
    DIVIDE (
        CALCULATE (
            [Total Sales],
            FILTER (
                ADDCOLUMNS (
                    ALLSELECTED ( Table1[Product] ),
                    "R", RANKX ( ALL ( Table1[Product] ), [Total Sales] + [ProductRank],, DESC )
                ),
                [R] <= __Curretnrank
            )
        ),
        __totalsales
    )

[Anonymous]

Hello @Fowmy    Incorporating those latest measures, unfortunately still gives me strange results.   Below I have sorted the pareto percentages.  Normally, you would see this cumulatively increasing.  But take a look at the rows with "44".    One appears at the top, and two others with this same row count appear at the bottom.   The top one has an item number starting with 715 and the other two are 1024 and 1023.     It still seems that somehow the ranking is being affected by the numeric (or alphanumeric) values of the product.  

[Fowmy]

@Anonymous 

If you can build a dummy simple two column table in Excel representing the similar product patter as per your original data and share, I will be able to check 

[Anonymous]

Hi @Fowmy      This would be representative of the data.   

Item	Lines
787525	10
787523B	10
CA-767	45
CA-769	45
13457B	19
12348	25
12348B	31

[Fowmy]

@Anonymous 

It works for me, 

[Anonymous]

Hi @Fowmy     Thanks for your help.   I have done everything I can to try and get my pareto to work.   My formulas are exactly the same, except for I am doing a count of rows, versus a sum of sales.   No matter what, I still get pareto percent values that repeat.   I've got someone else looking at a solution and evaluating why my version is not working....will report back once I have that solution.    But your solution definitely worked in your file...just not sure why it is not working in mine, which I hope to have an answer for.  

[Fowmy · Accepted solution]

@Anonymous 

It seems your data is not the same granularity as mine, I added a dimension table for the Products and repeated the fact table lines. Please the attached file. Created a new table and added new measures. Please check.