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

Combined actual and target charts

Very new to Power BI ... day 2 and counting

 

A simple question whgich I hope somebody can help me with.

 

I have a very simply (subset below) data structure.  I want to create a chart that compares acttual sales with target sales.

 

I have created 2 YTD measures on Sales and taget value and can generate individaul line charts with the values in the manner which I require.  

 

My question is how do I combine these in to a single chart, if this is possible?  I can't drag both measures on to the chart values area.

 

Thanks

 

Peter

 

pbi.jpgpbi2.PNG

 

1 ACCEPTED SOLUTION

Actually, is is NOT your measures or design but a limitation within Power BI that is causing the issue.  You can't use both Series and multiple measures (calculations) in the same Line chart.  

 

Here is a picture of a column based visual which kind of tells the story:

Clustered Column Chart, with Category and Month-end on X-Axis, and both YTD Actual and Target on Values.

Then use the Drill to explode out the axis, and on the X-Axis format, turn off "Concatenate Labels".

 

 

YTD_CompareBySeries.JPG

 

 

View solution in original post

3 REPLIES 3
BraneyBI
Kudo Commander
Kudo Commander

Welcome to Power BI!

All you need to do is pull both the actuals and Target into the values of the charts.  Ensure your Dates in the X-Axis come from the Dates table, then the series comes from the Salesperson table, and the reports will work.

 

In your case, your Dates and Saleperson tables are Dimensions which are shared tables,

Actual and Target are Facts which contain the Measures (things you are measureing) .  

Anytime you want to combine measures from both fact tables, you need to slice them by fields from the dimension tables.  

 

 

Barbara

Actually, is is NOT your measures or design but a limitation within Power BI that is causing the issue.  You can't use both Series and multiple measures (calculations) in the same Line chart.  

 

Here is a picture of a column based visual which kind of tells the story:

Clustered Column Chart, with Category and Month-end on X-Axis, and both YTD Actual and Target on Values.

Then use the Drill to explode out the axis, and on the X-Axis format, turn off "Concatenate Labels".

 

 

YTD_CompareBySeries.JPG

 

 

View solution in original post

pcav
Frequent Visitor

Barbara, thank you so much for taking your time out to reply. 

 

I'm still pretty hazy with the fact and dimensions but what you say makes absolute sense. 

 

But I still can't make this work. 

 

Everytime I select the "2nd" YTD measure field it "drops" in to the Tooltips value and the not the values area.  I don't think this is actually to do with measures I have created because I have the same issue with the "raw" values. 

 

If I remove the "salesman" from the legend then its fine I get what I want Target and actual on the same chart but its for all salesmen. 

 

I can put a slicer in and get the individual values that way but what I was trying for was for actual and target for each of the l salesmen on the same chart.

 

Thanks

 

Peter

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.