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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
PhillipB
Frequent Visitor

Average Line from another table

I am trying to create a Column and Line chart with information from 2 tables.  The first table has the date field and the main information with dates associated.  The second table has other information also with a date associated with in the same range.

 

When I then try to add a line from the second table and create an average it ignores the dates and simply provides an average of the entire date range shown on the chart.

 

If the tables are linked through a calendar table or directly with the dates then the average does not show at all.

 

Some pointers would be great.

 

 

5 REPLIES 5
PhillipB
Frequent Visitor

Yes your example works perfectly however for the data I am using it still is giving me some issues.  I have tried the method you have shown but it is not acting as it should.  I will send over the dashboard via a private message

Both your date columns were in DateTime format including a time component. That prevented them from successfully linking to the calendar table.

 

Sent you the file via DM.

lbendlin
Super User
Super User

Read about the TREATAS() function.

 

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Thanks @lbendlin 

 

I have looked at TREATAS() but I am not sure that this is giving me what I need.

 

DateIncident
1 jan 2022A
3 jan 2022B
8 jan 2022A
19 jan 2022A
2 feb 2022C
4 feb 2022A
8 feb 2022

B

10 feb 2022

C

14 feb 2022A

 

DateQuantity
2 jan 20223%
7 jan 20226%
9 jan 20225%
18 jan 20226%
4 feb 20225%
9 feb 20224%
11 feb 20226%
13 feb 20227%
14 feb 20223%

 

These two table are an example

 

Screenshot 2023-01-16 122807.png

 

And this is the kind of output but ideally I would like to show the line on the bar chart.  The line is an average of per month, the bars are a count per month.

 

I can link the data but if I do that, either directly or via a calendar table (with days) neither works correctly.

 

Regards
Phillip

Seems to work fine for me?

 

lbendlin_0-1673915593559.png

see attached.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors