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". 

Text visual with Pyramid Analytics

In PowerBI there is a real nice visualisation that only shows a single figure with data. For Example you can see the actual sales this year without having to filter, slice, dice or having to read a chart or grid.

The image shown is a power bi dashboard example with the gaphic I am refering to.

At this moment Pyramid Analytics supports a variety of graphs, grid styles, maps and KPI's but this visualisation is not possibly by default.

The below tutorial will explain shortly how to accomplish this result by performing some tricks.

Firstly, lets create a new bioXL file. Within this report add a measure on columns and for example Year on rows.

This grid will then look like this:

Now it's time for some styling magic.

In the GRID tab first pump up the font size (red square in below image)

Then hide the borders by making them transparent (blue square in below image)

Then make rows and columns background transparent (yellow square in below image)

Then make the row, column or both text transparent (purple square in below image)

After these steps the text visual is created. Result is shown below in bioXL and as example in bioPOINT

Now this is a rather simple excercise. With adding parameters you can easily interact with this data visualisation!