Dynamic pareto chart

Pareto charts are often used to be able to see which customer or products are responsible for 80% of the total revenue or something like that. Creating such a chart is kind a easy in Excel but can be a hurdle to create in a self service Business Intelligence tool.


Within Pyramid Analytics it is possible for power users, so called Analyst or professional, to create customer measures based on other measures and formulas.


To create this visualization in Pyramid Analytics, please follow the small tutorial below.

First, create a new bioXL file and put a single measure on columns and a hierarchy or attribute on rows. You can add some slicers if you want. Sort the rows based on the measure (descending)

Now lets run the query with a grid and bar chart as visuals. That should look like the image below.





What's next is to create a custom calculation. Click the green square to the right to create a custom measure. Notice the green squares, give it a name, set it to public and apply the format string.

Now add the formula below. The yellow text can be changed to a specific measure or you have to create a parameter for the measures with the name "Select a measure"

SUM

(

TOPCOUNT(

AXIS(1),

RANK(AXIS(1).ITEM(0).HIERARCHY.CURRENTMEMBER,AXIS(1),[Measures].[!@Select a Measure@!])

  

)  ,

   [Measures].[!@Select a Measure@!]

  )    

        / 

     

SUM(

{AXIS(1)},

[Measures].[!@Select a Measure@!]

  )  

When this measure is created, select it together with the measures that you referred to in the formula above. Because it's a percentage measure you probably wont see it in the chart. Change it to secondary axis by right clicking on the measure name in the legend and choose Edit series. Now give it a color, make it a line and you are done!!

The result will look like this:

And now the best part is that you can easily drilldown the hierachy or replace the row value with another attribute and the pareto will still work. Last note: If your dimensions contains a lot of values without data point. Please add a filter to the grid with "Filter above 0".