Pareto Chart is functional, but some categories are out of place.

[dtremmel96]

Hi! I've been using this measure for making Pareto charts for the past couple of weeks and I haven't noticed any issues, until now that is.

Here is my Data model I'm currently working with:

An example of what my data looks like:

ID	Room	% Yield
322	Acrylic Filling	100%
311	Multi-National	50%
312	PO-Paste Cartoner	88%
314	PO-Paste Filling	101%
310	Offline Packaging	0%
320	Paste Room 1	0%
324	Liquid Filling	100%
316	Upper Packaging	50%
309	Main Line Packaging	0%

My data is structured such that:
1. Everyday new data is added for that day

2. Each Room will have new data each day

My visuals are structured such that the generated Pareto charts go from the largest % Yield on the left, to the smallest % Yield on the right.

Here is my current code I am using to achieve this Pareto line:

Pareto % Yield =
VAR _Current =
    SELECTEDVALUE ( 'Daily Production Metrics'[Room] )
RETURN
    IF (
        NOT ISBLANK ( _Current ),
        VAR _Allselected =
            ALLSELECTED ( 'Daily Production Metrics' )
        VAR _Table =
            ADDCOLUMNS (
                _Allselected,
                "Total Yield", [Total Yield % Amount],
                "Rank",
                    RANKX ( _Allselected, [Total Yield % Amount],, DESC )
                        + RANKX ( _Allselected, 'Daily Production Metrics'[ID],, DESC ) / 100000
            )
        VAR _CurrentPos =
            MAXX ( FILTER ( _Table, 'Daily Production Metrics'[Room] = _Current ), [Rank] )
        VAR _CumCount =
            SUMX ( FILTER ( _Table, [Rank] <= _CurrentPos ), [Total Yield] )
        VAR _TotalCum =
            CALCULATE ( [Total Yield % Amount], _Allselected )
        VAR _Result =
            DIVIDE ( _CumCount, _TotalCum )
        RETURN
            _Result
    )

The Total Yield % Amount measure I have is just:

SUM('Daily Production Metrics'[% Yield])

Now that the set-up is out of the way, here's an example of what my chart is giving today:

Like I said previously, this has been working ok so far, but today I noticed this issue happening where one of the yield %'s for my rooms becomes out of order. I thought at first it may be an issue with my Room names for some reason, but after looking at another graph, the same thing is happening to a different room as well.

For this Pareto to work correctly, it needs to consistently show all of the rooms in descending order based on the % yield, while still calculating the cumulative percentage correctly across the rooms.

I'm pretty stuck and I've been searching for alternative ways that could potentially solve this issue, but sadly haven't found anything yet. Any help on figuring out the problem here would be super appreciated! And also, if I forgot to give any information please let me know and I'll do my best to explain further or provide more info.

DISCLAIMER: I originally got the DAX code from watching a helpful Youtube video on someone trying to solve an issue of if you have the same y-axis value for two different categories, which is what I was originally running into here when I was first starting to visualize this dataset. This is because my data has a very high chance of giving the same y-axis values across different categories.

Thank you in advance for any help you can provide!

[Ashish_Mathur]

Hi,

Share some data to work with.

[lbendlin]

Your formula is a little on the heavy side.  Ok to slim it down a bit?

Pareto = 
var a = sum('Table'[% Yield])+sum('Table'[Column])
var b = calculatetable(summarize('Table','Table'[Room],"s",sum('Table'[% Yield])+sum('Table'[Column])),ALLSELECTED())
var c = Filter(b,[s]>=a)
return sumx(c,[s])

Ties can be addressed by adding small random values.

Column = RAND()*0.01

What is the business statement behind your Pareto?  Why are you summing up percentages?

[dtremmel96]

Hi @Ibendlin 

To be frank with you, I don't quite understand the reasoning behind wanting these percentages as paretos, but it is something my boss wants to see.  I don't believe I am seeing the correct thing when I introduce your code to my current model. The pareto is giving me values that sum up the percentages, when I need a cumulative summing of the percentages up to 100% at the last category. Essentially to look similarly to this:

So in this example, the first category on the far left makes up 27.74% of the Top 10 Backorders at or company. The value at the second category is 46.87%, which is the percentage of the first category plus the percentage of the second. These percentages are all in relation to whatever is on the visual at that time.

So in terms of the data I supplied originally:

ID	Room	% Yield	Cumulative percentage
322	Acrylic Filling	100%	41.10%
311	Multi-National	50%	89.77%
312	PO-Paste Cartoner	88%	79.55%
314	PO-Paste Filling	101%	20.65%
310	Offline Packaging	0%	100%
320	Paste Room 1	0%	100%
324	Liquid Filling	100%	61.55%
316	Upper Packaging	50%	100%
309	Main Line Packaging	0%	100%

I apologize if it's not clear, I'm struggling to explain exactly what it is I need to do.

[lbendlin · Accepted solution]

You only need to change the Pareto measure slightly.

Pareto = 
var a = sum('Table'[% Yield])+sum('Table'[Column])
var b = calculatetable(summarize('Table','Table'[Room],"s",sum('Table'[% Yield])+sum('Table'[Column])),ALLSELECTED())
var c = Filter(b,[s]>=a)
return divide(sumx(c,[s]),sumx(b,[s]))

[dtremmel96]

Ah, that's much more concise than what I had made to solve the issue, haha. Thank you so much for your help!