cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mafaber Frequent Visitor
Frequent Visitor

Visualizing series with different X-axis on the same chart

Hi,

 

my question is not really technical, but a visualizing problem. I have a report containing many KPIs, and one is a hard nut to crack for me.

Each quarter closing month (3, 6, 9, 12th month) I receive 3 data (all in percentages):

  • an actual for the closed quarter
  • an estimate for the next quarter
  • an estimate for the full year

Each non-quarter closing month (1, 2, 4, ..., 11th month) I receive 2:

  • an estimate for the current quarter
  • an estimate for the full year

Things I have to visualize:

  • all the quarter actual results
  • an estimate for the current, non-closed quarter
  • two estimates for the full year, the latest and the previous month's

The latest data I have at the moment is September (P09), so at this point I'd like to visualize Q1, Q2, Q3 actual, Q4 estimate, and P08, P09 full year estimate.

You can see my current state on the picture below.
KPI_state1.JPG

I achieved this by 2 different chart on each other, a Clustered Column and a Line chart, the Clustered Column contains all the Quarterly data, the Line chart contains the Monthly full year estimate data, but I have many problems with this. The two value for the Line are P08 and P09 and the X-axis means the time the prediction was made, while as for the Column chart the meaning of the X-axis is the period we are making the prediction for, so I find this very confusing, but I'm yet to find a better solution.

(All the numbers are percentages, thus the full year estimate and actuals can share a Y-axis. Of course if these were absolute values, then it wouldn't make any sense as the full year would be a much higher value.)

 

 

Just to show you another KPI, where the actuals are monthly values, hence the X-axis can be shared and the same approach works just fine:

KPI2_state1.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The series I have to visualize is fixed, but I'm open to any kind of visualization, if you have a better idea.

 

Thank you.

4 REPLIES 4
mafaber Frequent Visitor
Frequent Visitor

Visualizing series with different X-axis in one chart

Hi,

 

my question is not really technical, but a visualizing problem. I have a report containing many KPIs, and one is a hard nut to crack for me.

Each quarter closing month (3, 6, 9, 12th month) I receive 3 data (all in percentages):

  • an actual for the closed quarter
  • an estimate for the next quarter
  • an estimate for the full year

Each non-quarter closing month (1, 2, 4, ..., 11th month) I receive 2:

  • an estimate for the current quarter
  • an estimate for the full year

Things I have to visualize:

  • all the quarter actual results
  • an estimate for the current, non-closed quarter
  • two estimates for the full year, the latest and the previous month's

The latest data I have at the moment is September (P09), so at this point I'd like to visualize Q1, Q2, Q3 actual, Q4 estimate, and P08, P09 full year estimate.

You can see my current state on the picture below.
KPI_state1.JPG

I achieved this by 2 different chart, a Clustered Column and a Line chart, the Clustered Column contains all the Quarterly data, the Line chart contains the Monthly full year estimate data. I have many problems with this. The two value for the Line are P08 and P09 and the X-axis means the time the prediction was made, while for the Column chart the meaning of the X-axis is the time we are predicting, so I find this very confusing, but I'm yet to find a better solution. Also I'm not sure where to put the line on the X-axis.

(All the numbers are percentages, thus the full year estimate and actuals can share a Y-axis. Of course if these were absolute values, then it wouldn't make any sense as the full year would be a much higher value.)

 

 

Moving the quarterly and monthly values to two different charts isn't working, because I wouldn't use a whole chart for just one line with 2 values.

Just to show you another KPI, where the actuals are monthly values hence the X-axis can be shared and the same approach works just fine:

KPI2_state1.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The series I have to visualize is fixed, but I'm open to any kind of visualization, if you have a better idea.

 

Thank you.

v-danhe-msft Super Contributor
Super Contributor

Re: Visualizing series with different X-axis on the same chart

Hi @mafaber,

Could you have used the "Line and clustered column chart"? 

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-combo-chart#create-a-combo-...

And I could not understand what is your desired result, could you please share me some sample data and post your desired result if possible?

 

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mafaber Frequent Visitor
Frequent Visitor

Re: Visualizing series with different X-axis on the same chart

Hi @v-danhe-msft,

 

I created a data sample file that you can access here: https://drive.google.com/open?id=1oIVn_VKxFP0Fz5383mmCvxM5cGDSMUd_

You can also find a small explanation for the meaning of the columns in a textbox below the table.

 

The desired results is just visualizing the KPI in a clear and understandable way, I listed the things I have to see in my post, I don't have a fixed chart type I'd have to stick to.

The whole problem is that I couldn't visualize Quarter actuals, a Quarter estimate and two full year estimates in a way I'd be satisfied with. Smiley Happy

I try to describe things as clear as possible, but please let me know if you have anymore questions Smiley Happy

 

Thank you.

 

Regards,

Marton

 

v-danhe-msft Super Contributor
Super Contributor

Re: Visualizing series with different X-axis on the same chart

Hi @mafaber,

From your description, I could not understand how to judge below conditions:

estimate for the current, non-closed quarter
latest and previous estimate for the full year

Could you please offer me more detail information?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 51 members 1,308 guests
Please welcome our newest community members: