Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more

Reply
El_Bastien
Frequent Visitor

Pareto Diagram

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:

Screenshot 2024-10-10 at 12.21.54 p.m..png


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!

3 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

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

View solution in original post

v-xuxinyi-msft
Community Support
Community Support

Hi @El_Bastien 

 

Thanks for the reply from lbendlin.

 

The following testing is for your reference.

 

My sample:

vxuxinyimsft_0-1728616613826.png

 

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:

vxuxinyimsft_0-1729243981641.png

 

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.

View solution in original post

sio2Graphs
Frequent Visitor

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

 

graph and table.png

 

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

Share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
sio2Graphs
Frequent Visitor

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

 

graph and table.png

 

v-xuxinyi-msft
Community Support
Community Support

Hi @El_Bastien 

 

Thanks for the reply from lbendlin.

 

The following testing is for your reference.

 

My sample:

vxuxinyimsft_0-1728616613826.png

 

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:

vxuxinyimsft_0-1729243981641.png

 

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
Super User
Super User

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.