Does anyone have a good formula for a pareto chart in Birst?

Dan_Rosen
Dan_Rosen Member Posts: 13
in Birst

Does anyone have a good formula for a pareto chart in Birst? This is often requested and we always have to take it to excel to be created. Before I spin my wheels, I figure someone may have solved this issue before with a formula to create the pareto line on top of a standard column chart.

Best Answer

  • CindyHoskey
    CindyHoskey Member Posts: 3
    Answer ✓

    Hey, Dan. Here's my way of doing it.

    1. Add your measure and your category into a report (I usually start with tabular). In my example, the measure is named "Count", so the BQL for it is [Sum: Count], and my attribute is [Type.Issue] (bad names, I know). There are 10 different Issues in the data set.
    2. Sort by your measure descending.
    3. The cumulative percent calculation I used is this: RSUM(10,[Sum: Count])/[Sum: Count]{[Type.Total]}

    The numerator is RSUM(10,[Sum: Count]), a running sum of the measure named Count, with a window size of 10 because I have a total of 10 issues.

    The denominator is [Sum: Count]{[Type.Total]} which is a positional calc.

    4. Turn it into a column chart, then set the cumulative percent as a line and viola! You have a Pareto chart.

    Raw data:

    image.png

    Pareto chart:

    image.png

    Hope this helps!

    - Cindy

Answers

Categories