Calculating Cumulative Percentage & Creating a Pareto Chart

Calculating Cumulative Percentage:

Assuming you have a dataset in Power BI with a column you want to calculate the cumulative percentage for, follow these steps:

1. In the Power BI Desktop, click on the "Model" view (left side menu) to access the data model.

2. Select your data table from the "Fields" pane.

3. Click on "New Measure" in the "Modeling" tab of the top ribbon.

4. Use the following DAX (Data Analysis Expressions) formula to create a measure for cumulative percentage:

Cumulative Percentage = 
DIVIDE(
    SUM('YourTable'[ColumnYouWantToCalculatePercentageFor]),
    CALCULATE(SUM('YourTable'[ColumnYouWantToCalculatePercentageFor]), ALL('YourTable'))
)

Replace 'YourTable' with the name of your data table and 'ColumnYouWantToCalculatePercentageFor' with the actual column name for which you want to calculate the cumulative percentage.

5. Click "OK" to create the measure.

Creating a Pareto Chart:

Once you have the cumulative percentage measure ready, you can use it to create a Pareto chart, which combines both a column chart and a line chart. The column chart represents the individual categories, and the line chart represents the cumulative percentage.

Here's how you can do it:

1. Go to the "Report" view in Power BI.

2. Drag the column you want to analyze (e.g., categories) into the "Axis" or "Legend" field well of the visualization pane to create the column chart.

3. Drag the "Cumulative Percentage" measure you created earlier into the "Line values" field well of the visualization pane. Power BI should automatically detect that it needs to use a line chart for this measure.

4. To make the chart more informative, you may want to sort the categories in descending order of the values. You can do this by clicking on the ellipsis (three dots) in the "Axis" or "Legend" field well and selecting "Sort ascending" or "Sort descending" based on your preference.

5. Optionally, you can format the chart, add titles, and customize the visual aspects to make it visually appealing and easy to understand.

With these steps, you should now have a Pareto chart showing the cumulative percentage alongside the individual categories in your Power BI report.