Forum Discussion
Creating Cumulative Graphs
Hi All
I've looked through the posts and although similar questions have been asked I can't find one that matches what I'm trying to do but sorry if this has been asked in a different way.
I want to create a visual showing 2 different line graphs pulling data from 2 different tables, I can get them working individually and I suspect the problem is down to the "Date" fields.
My data looks like this:
One table is for time spent working on a job, the other is invoices against that job and as you can see the dates don't match.
If I create a graph using NDate from table A the time graph looks to be correct but when I ad the billing totals I just get a straight line across the graph:
I'm guessing this is because its trying to plot the invoice amount against the time worked date, is there any way to get this to work or do I need to go back to 2 seperate visuals?
Thanks
Mick
Did you use the date column from the calendar table in your visual and relationship is one to many single direction from the dim to fact tables? Also, since you want the cumulative (from the very first date), apply FILTER to the whole table instead of to just a specific column.
Please see the attached pbix.
12 Replies
Create a date table and mark it as a date table. There are lots of videos or tools you can use to do this.
Link your date table to both of your existing tables and use columns from the date table on your charts.
Hi mick_lowe - No, the measure doesn't need to be moved. A measure can live in any table ,
The key thing is to use the Date column from the Calendar table on the visual and ensure both fact tables have active relationships to it. Moving measures to a dedicated Measures table is purely for organization and has no impact on the result.
Hope this helps.
Thankyou rajendraongole1 , johnt75 for Addressing the issue.
Hi mick_lowe ,
Thank you for reaching out to Microsoft Fabric Community Forum,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
Regards,
Chaithanya
Hi rajendraongole1 johnt75 v-kathullac
Thanks for all the replies, this is a bit of a "pet project" and not my full time job, so sorry for not coming back to you sooner.
I couldn't get this to work using the suggetions, what I ended up doing was creating 2 fields on each table:
Total Time Spent = SUM('zprojects'[timespent])And:Cumulative Time Spent = CALCULATE( [Total Time Spent], FILTER( ALL('Calendar'[Date]), 'Calendar'[Date] <= MAX('Calendar'[Date]) ) )This works for some:
but not others so I'm currently trying to work out why:Did you use the date column from the calendar table in your visual and relationship is one to many single direction from the dim to fact tables? Also, since you want the cumulative (from the very first date), apply FILTER to the whole table instead of to just a specific column.
Please see the attached pbix.
Hi mick_lowe ,
Thank you for reaching out to Microsoft Fabric Community Forum,
- The query fails with the error: "The query has exceeded the available resources." The error indicates that the query exceeded the available memory, CPU, or capacity limits.
- The issue may be related to Large dataset or high data volume, Complex DAX measures or Power Query transformations, Inefficient data model or expensive query execution,Power BI/Fabric capacity or resource limitations.
Kindly check the above debug steps to resolve the Exceed issue.Let us know if you need any further assistance?
Thanks,
Chaithanya.
Thankyou @rajendraongole1 , @johnt75 , danextian for Addressing the issue.
Hi @mick_lowe ,
Thank you for reaching out to Microsoft Fabric Community Forum,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
Regards,
Chaithanya
