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!

Pyramid Analytics - bioXL - User options

In bioXL users have several options to personalize their work environment. These settings are kind a hidden if you don't know that they exist. I recently wrote a blog regarding changing the default values for columns & rows in bioXL. This post will highlight all user options that are available in bioXL.

Query execution

Default slicer execution: Default slicer Execution will ensure that changing any value in a slicer the report will be re-executed. This could be nice to turn off while in pure development. This option can also be toggled at the slicer properties

Default non Empty Mode: Selecting this feature will prevent that empty rows are shown in the results. This setting can also be toggled at the selections pane. 

Default selection Tree Execution: Default selection Tree Execution will ensure that changes in the selection pane will re-execute the report. 

Slicers

Default visualisation: Default slicer that will be selected when adding a value to the slices in the selection pane

Actions

Default Drill-through Rows: Default amount of rows that are returned while using actions

Report Layout

Default view: Default screen that is selected when creating a new bioXL file

Query options

Query size warnings: This feature will allow users to supress warning messages on selecting a large volume of rows. 

As mentioned I have allready explained the default query model. Navigate to this post by clicking the following link . Default value for rows & columns

Pyramid Analytics - bioXL - Default values for rows & columns

Creating bioXL files is easy. Within a few minutes you've created a new report and you can start with the next one.

I'm working with Pyramid Analytics for some time now and there is a hidden feature that's kind a handy when creating many bioXL files. In the bioXL ribbon there is a possibility to set another default query model.

First, lets navigate to the hidden feature. Create a new bioXL, navigate to the "Query" tab and click on the "User options" icon. In this menu you probably will find other options that might be useful. I will discuss these in another post



The default model will put the first dimension on rows, second dimension on columns and first measure in subsets. Consider changes this query model based on your preferences.

Available models:

Columns First dimension Rows Second dimension Subsets Measure Columns Time dimension Rows First dimension Subsets Measure Columns Time dimension Rows Measure Subsets - Columns First dimension Rows Time dimension Subsets Measure Columns Measure Rows Time dimension Subsets -

First dimension will be the first hierachy (if available) from the first dimension based on text sorting
Time dimension will be the dimension with time intelligence attached
Measure will be the first created measure in the cube
















Personally i prefer the fifth option. Measure on columns vs time on rows.