cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pcav Frequent Visitor
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

Accepted Solutions
BraneyBI Member
Member

Re: Combined actual and target charts

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

 

 

3 REPLIES 3
BraneyBI Member
Member

Re: Combined actual and target charts

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

Highlighted
pcav Frequent Visitor
Frequent Visitor

Re: Combined actual and target charts

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

 

BraneyBI Member
Member

Re: Combined actual and target charts

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

 

 

Helpful resources

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.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 153 members 1,981 guests
Please welcome our newest community members: