Forum Discussion

thewookie's avatar
thewookie
Helper I
5 months ago
Solved

Help with Pareto chart

Hello:

 

I am decent with Power BI, DAX and M, but I have been messing with a problem for a while. I have used Google and Copilot looking for help without success. I has asked to create two pareto charts and then only show those bars that made up 80% of the total. The first two pictures show the first chart in full view and then with a visual filter showing the less than equal to 80% for the line y-axis. The second pair of images shows the same charts with the column legends turned on. I have messed with this for hours and tried to math my way out of this without success.

 

X-axis:

2 Year Table = filter('PLA_POWER_BI',PLA_POWER_BI[Event Date]>=[ParetoStart] && 'PLA_POWER_BI'[Event Date]<=[ParetoEnd] && 'PLA_POWER_BI'[Slowdown_Quantity]>=0 )

 

Y-Axis:

Unplanned Production Loss (MT) = calculate([Production Loss (MT)],FILTER('2 Year Table','2 Year Table'[PERK]<> "Low Sales Demand" && '2 Year Table'[PERK]<> "Planned Maintenance" && '2 Year Table'[PERK]<> "Plant Turnaround (Strategic Outage)" && '2 Year Table'[PERK]<> "Weekend") )

 

Line y-Axis:

Unplanned Cumulative Production Loss % = DIVIDE( [Unplanned Cumulative Production Losses], [Total Losses] )

 

where:

Unplanned Cumulative Production Losses = VAR CurrentRank = RANKX( ALLSELECTED('2 Year Table'[PERK]), [Unplanned Production Loss (MT)], , DESC, Dense ) RETURN CALCULATE( [Unplanned Production Loss (MT)], FILTER( ALLSELECTED('2 Year Table'[PERK]), // PLA_POWER_BI, RANKX(ALLSELECTED('2 Year Table'[PERK]), [Unplanned Production Loss (MT)], , DESC, Dense) <= CurrentRank && [Unplanned Production Loss (MT)] >0 ) ) and: Total Losses = calculate([Unplanned Production Loss (MT)], FILTER(ALL('2 Year Table'[PERK]),'2 Year Table'[PERK]<> "Low Sales Demand" && '2 Year Table'[PERK]<> "Planned Maintenance" && '2 Year Table'[PERK]<> "Plant Turnaround (Strategic Outage)" && '2 Year Table'[PERK]<> "Weekend" && [Unplanned Production Loss (MT)]>0 ))

Also, If i select one of the individual lines associated with the plant on the chart with column legends, the line becomes flat:Line selected .png

 

Need Help
  • MFelix's avatar
    MFelix
    5 months ago

    Hi thewookie ,

     

    Believe I have found the question and is related with context of your calculations:

    • Total Losses current syntax:
    Total Losses=
CALCULATE (
    [Production Loss (MT)],
    FILTER (
        ALLSELECTED ( 'Partial Data Set' ),
        'Partial Data Set'[PERK] <> "Low Sales Demand"
            && 'Partial Data Set'[PERK] <> "Planned Maintenance"
            && 'Partial Data Set'[PERK] <> "Plant Turnaround (Strategic Outage)"
            && 'Partial Data Set'[PERK] <> "Weekend"
            && [Production Loss (MT)] > 0
    )
)
    • The ALLSELECTED returns all rows in a table, or all the values in a columns, ignoring any filters that might have been applied inside the query, but keeping filters that come from outside. This means that when you apply the filter for the 80% that filter context is taken into consideration so your overall calculation is not on top of the full data but on the specific data you filter out

     

    Then you have the question about the running loss:

    • This calculation is done picking up the Running Loss measure and the getting the total losses, however when you filter the calculations based on the percentage the results get changed because the Running loss is nonw getting not only a filter at perk level but also at line level because it's part of the context

     

    If you change the measure to the following you should get the proper result:

    Total Losses Adjusted=
CALCULATE (
    [Production Loss (MT)],
    FILTER (
        ALL ( 'Partial Data Set' ),
        'Partial Data Set'[PERK] <> "Low Sales Demand"
            && 'Partial Data Set'[PERK] <> "Planned Maintenance"
            && 'Partial Data Set'[PERK] <> "Plant Turnaround (Strategic Outage)"
            && 'Partial Data Set'[PERK] <> "Weekend"
            && [Production Loss (MT)] > 0
    )
)


Running Loss % Adjusted = 
 DIVIDE(
    CALCULATE([Running Loss Measure], REMOVEFILTERS('Partial Data Set'[Line])),
    [Total Losses Adjusted]
 )


    Has you can see the left side (filter adjusted measures) now maches the left side (no filter original measures).

     

    Be aware that this may need some more tweeks based on your model. 

     

    See PBIX file attached.

     

26 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 months ago

    Hi thewookie ,

     

    Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

    If the information is sensitive please share it trough private message.

    • thewookie's avatar
      thewookie
      Helper I
      5 months ago

      The file is huge and our security it strict.  I'm not sure how I do that.

       

      • MFelix's avatar
        MFelix
        Super User
        5 months ago

        Hi thewookie ,

         

        I understand, try and check out this video below that allows you to do a Pareto chart using visual calculations much more easier and it's always based on the data that is on the visual.

         

        If you need additional help please let me know.

  • thewookie's avatar
    thewookie
    Helper I
    5 months ago

    I just confirmed that there is virtually no way to do this with our corporate security

     

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    5 months ago

    Hi thewookie ,

    Since your dataset is quite large, it might help to isolate the problem a bit. If possible, try creating a separate PBIX with only the relevant tables and fields needed for the Pareto calculation.

    Work on building and fixing the Pareto visual there first, it’ll be much easier to debug without all the additional complexity. Once you’ve nailed down the logic and behavior, you can bring the same approach back into your main file.

    This usually makes it a lot quicker to identify what’s actually causing the issue.

    I hope this information helps. Please do let us know if you have any further queries.
    Thank you

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    5 months ago

    Hi thewookie 

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.


    Thank you.

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    5 months ago

    Hi thewookie 

    May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.


    Thank you