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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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