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
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
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.