Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hylim9195
New Member

How to plot data from two different tables on a single ling and clustered chart

Hi power bi community, I am trying to plot both actual value(Dwell time) and forecasted value (Point Forecast) on the same graph as shown in the screenshot

image.png

However, the graph is not displaying correctly. Only one of the variables is displayed correctly. Both variables are using hourly time stamps in the same format. What am i doing wrongly?

 

 

1 ACCEPTED SOLUTION

Your using the hourly timestamp form the Data Table on your X axis. So you when you add the point forecast PowerBI is not applying that filter context to your point forecast.  You need to use the Date-Time hourly index from your date table for X Axis of your chart (assuming the two data sources are related via the date table so powerBI knows how to correctly apply the filter context so what you are getting is the total for all time as a value. You also need to go into the Formatting and turn off the Secondary axis for the Y axis so both are plotted on the same scale. Your slicers will also need to be slicing bridge tables that relate the two data sets if they are not already.

 

If your date table is not a Date-Time table with hourly granularity it won't work.

 

You could also try making the relationship for yoru Dwell Time to the Date Table bi-diretional to force the filter context to flow up to the date dable which will then force it down into your other table.

 

Tip, you can't always but the item your relating on the axis or define enough bi-directional filters to get the results you want.  If you run into this then you may need to use the trick of specifying the other table as a filter term in a calculate for your measures this also forces the filter context. 

 

Dealing with multiple fact tables can be tricky here is a tutorial I put together for a user group I'm part of.

 

https://filedrop.telusa.com/message/h9xIgqeKsAd4uC5REJ4Fx4

 

 

 

 

 

View solution in original post

3 REPLIES 3
Seward12533
Solution Sage
Solution Sage

@hylim9195 can you clarify what your expected results are? is your point forecast supposed to be just a target line? And is the correct value shown? Is it supposed to look like the chart below with the target line on it? Do you want them on teh same scale? Are you seeing it diplayed by Days but only want hours?

The expected values are shown in the smaller chart below. I want to represent the forecasted data as a line on the graph above. The forecast dataset only contains values from 4 jan to 5 jan. Yes I want them on the same scale. Both are already using a hourly timestamp.

Your using the hourly timestamp form the Data Table on your X axis. So you when you add the point forecast PowerBI is not applying that filter context to your point forecast.  You need to use the Date-Time hourly index from your date table for X Axis of your chart (assuming the two data sources are related via the date table so powerBI knows how to correctly apply the filter context so what you are getting is the total for all time as a value. You also need to go into the Formatting and turn off the Secondary axis for the Y axis so both are plotted on the same scale. Your slicers will also need to be slicing bridge tables that relate the two data sets if they are not already.

 

If your date table is not a Date-Time table with hourly granularity it won't work.

 

You could also try making the relationship for yoru Dwell Time to the Date Table bi-diretional to force the filter context to flow up to the date dable which will then force it down into your other table.

 

Tip, you can't always but the item your relating on the axis or define enough bi-directional filters to get the results you want.  If you run into this then you may need to use the trick of specifying the other table as a filter term in a calculate for your measures this also forces the filter context. 

 

Dealing with multiple fact tables can be tricky here is a tutorial I put together for a user group I'm part of.

 

https://filedrop.telusa.com/message/h9xIgqeKsAd4uC5REJ4Fx4

 

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.