Pareto Diagram

[El_Bastien]

Hello! I'm running into some issues trying to create a Pareto Diagram. 

I've created this DAX formula to create the Cumulative Count of my variables. When I wanted to check if the formula was running as it should, I saw this:

 
As shown in the picture above, the line from the second to the third bar automatically adds up to 130 automatically, even though it should be 115 and then 130 when calculating the cumulative with the 4th bar.

This is the DAX code I've used.

Pareto =

IF(
ISINSCOPE('Data for PBI'[Question 1 Answers]),
VAR __AllSelectedAns = ALLSELECTED('Data for PBI'[Question 1 Answers])
VAR __AnsTable = ADDCOLUMNS(__AllSelectedAns, "Count",[Q1 Total Count])
VAR __CurrentCount1 = [Q1 Total Count]
VAR __CumQ1Table = FILTER(__AnsTable, [Count] >= __CurrentCount1)
VAR __CumQ1 = SUMX(__CumQ1Table, [Count])
RETURN
__CumQ1
)

Do you have any ideas on how to solve this?

If you have an idea on how to run a DAX code for the % for the Pareto Diagram, that would be very useful too!

Thank you!

[lbendlin · Accepted solution]

You need a tie breaker. Add a small random number to each of the values.

[v-xuxinyi-msft · Accepted solution]

Hi @El_Bastien 

Thanks for the reply from lbendlin.

The following testing is for your reference.

My sample:

Create two measures as follow:

Your [Q1 Total Count] looks like a measure, and since I don't know its calculation logic, I created [Q1 Total Count1] to restore your scene, and in your scene, you can use [Q1 Total Count] directly.

Pareto = 
VAR _AllSelectedAns = 
    ALLSELECTED('Data for PBI'[Question 1 Answers])
VAR _CurrentCount1 = 
    [Q1 Total Count1]
VAR _CumQ1Table = 
    FILTER(
        _AllSelectedAns,
        [Q1 Total Count1] <= _CurrentCount1
    )
VAR _CumQ1 = 
    SUMX(
        _CumQ1Table, 
        [Q1 Total Count1]
    )
RETURN
IF(
    ISINSCOPE('Data for PBI'[Question 1 Answers]),
    _CumQ1
)

Pareto % = 
VAR _TotalCount = 
    CALCULATE(
        SUM('Data for PBI'[Q1 Total Count]),
        ALL('Data for PBI'[Question 1 Answers])
    )
VAR _CumulativeCount = [Pareto]
RETURN
DIVIDE(_CumulativeCount, _TotalCount, 0)

Output:

Best Regards,
Yulia Xu

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

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[sio2Graphs · Accepted solution]

Hello, 

You can use visual "Pareto+" to create a graph and table.  The visual with do all of the calculations for you.  You can also use "Pareto by sio2Graphs" to produce the graph, no need for measures.

😀

sio2Graphs

sio2graphs on AppSource 

Data on Git

[v-xuxinyi-msft · Accepted solution]

Hi @El_Bastien 

Thanks for the reply from lbendlin.

The following testing is for your reference.

My sample:

Create two measures as follow:

Your [Q1 Total Count] looks like a measure, and since I don't know its calculation logic, I created [Q1 Total Count1] to restore your scene, and in your scene, you can use [Q1 Total Count] directly.

Pareto = 
VAR _AllSelectedAns = 
    ALLSELECTED('Data for PBI'[Question 1 Answers])
VAR _CurrentCount1 = 
    [Q1 Total Count1]
VAR _CumQ1Table = 
    FILTER(
        _AllSelectedAns,
        [Q1 Total Count1] <= _CurrentCount1
    )
VAR _CumQ1 = 
    SUMX(
        _CumQ1Table, 
        [Q1 Total Count1]
    )
RETURN
IF(
    ISINSCOPE('Data for PBI'[Question 1 Answers]),
    _CumQ1
)

Pareto % = 
VAR _TotalCount = 
    CALCULATE(
        SUM('Data for PBI'[Q1 Total Count]),
        ALL('Data for PBI'[Question 1 Answers])
    )
VAR _CumulativeCount = [Pareto]
RETURN
DIVIDE(_CumulativeCount, _TotalCount, 0)

Output:

Best Regards,
Yulia Xu

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

[lbendlin · Accepted solution]

You need a tie breaker. Add a small random number to each of the values.