Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hello,
I have a set of data which I am trying to show in a line graph visual. I want to be able to show 2 separate lines:
- Value per month
- Cumulative Value which obviously increases per month
Columns I need to use in this visual are:
* Project 'B00 Tabulated' [Project Definition]
* Date (by Month) 'B00 Tabulated' [Date]
* Value 'B00 Tabulated' [Baseline Forecast]
I have a date table relationship linked to the date column ('Calendar' [Date]), and the project column also links to relationships with other project columns in different sheets so the slicer works fine, I just can't get the totals to show correctly.
The data is tabulated so each project has 12 lines, one per month, to show the monthly value per project.
Can anybody suggest the correct dax measures to achieve this? (Redacted snip of the data model below)
Solved! Go to Solution.
Hi @kristi_in_heels,
Since you're using inactive relationship between 'Calendar' and 'Table', please modify the measures as below:
Forecast Value =
CALCULATE(
SUM('Table'[Baseline Forecast]),
USERELATIONSHIP('Calendar'[Date], 'Table'[Date]))
Cumulative Value =
var maxdate= MAX('Calendar'[Date])
return
CALCULATE(
SUM('Table'[Baseline Forecast]),
FILTER(
ALL('Calendar'),
'Calendar'[Date] <= maxdate),
USERELATIONSHIP('Calendar'[Date], 'Table'[Date]))
Please try if this works for you. If not, please share dummy data and let us know more info about your chart.
Thanks.
Cumulative Value =
var maxdate= MAX('Table'[Date])
return
CALCULATE(
SUM('Table'[Value]),
FILTER(
ALL('Table'),
'Table'[Date] <= maxdate))
Thank you for your response.
The calculation works to generate the total cumulative but the line remains flat at the total 12 month value across every month (purple straight line in the image below) as opposed to an up-curve over the entire period, reaching that point in the last month.
Sample data is below for reference:
Period | Project Definition | Project | Month | Baseline Forecast |
Budget1 | 1 | Name 1 | July | 1000 |
Budget1 | 2 | Name 2 | July | 1000 |
Budget1 | 3 | Name 3 | July | 1000 |
Budget1 | 4 | Name 4 | July | 1000 |
Budget1 | 5 | Name 5 | July | 1000 |
Budget1 | 1 | Name 1 | August | 1500 |
Budget1 | 2 | Name 2 | August | 1500 |
Budget1 | 3 | Name 3 | August | 1500 |
Budget1 | 4 | Name 4 | August | 1500 |
Budget1 | 5 | Name 5 | August | 1500 |
Budget1 | 1 | Name 1 | September | 2000 |
Budget1 | 2 | Name 2 | September | 2000 |
Budget1 | 3 | Name 3 | September | 2000 |
Budget1 | 4 | Name 4 | September | 2000 |
Budget1 | 5 | Name 5 | September | 2000 |
Budget1 | 1 | Name 1 | October | 2500 |
Budget1 | 2 | Name 2 | October | 2500 |
Budget1 | 3 | Name 3 | October | 2500 |
Budget1 | 4 | Name 4 | October | 2500 |
Budget1 | 5 | Name 5 | October | 2500 |
Budget1 | 1 | Name 1 | November | 3000 |
Budget1 | 2 | Name 2 | November | 3000 |
Budget1 | 3 | Name 3 | November | 3000 |
Budget1 | 4 | Name 4 | November | 3000 |
Budget1 | 5 | Name 5 | November | 3000 |
Budget1 | 1 | Name 1 | December | 3500 |
Budget1 | 2 | Name 2 | December | 3500 |
Budget1 | 3 | Name 3 | December | 3500 |
Budget1 | 4 | Name 4 | December | 3500 |
Budget1 | 5 | Name 5 | December | 3500 |
Budget1 | 1 | Name 1 | January | 4000 |
Budget1 | 2 | Name 2 | January | 4000 |
Budget1 | 3 | Name 3 | January | 4000 |
Budget1 | 4 | Name 4 | January | 4000 |
Budget1 | 5 | Name 5 | January | 4000 |
Budget1 | 1 | Name 1 | February | 4500 |
Budget1 | 2 | Name 2 | February | 4500 |
Budget1 | 3 | Name 3 | February | 4500 |
Budget1 | 4 | Name 4 | February | 4500 |
Budget1 | 5 | Name 5 | February | 4500 |
Budget1 | 1 | Name 1 | March | 5000 |
Budget1 | 2 | Name 2 | March | 5000 |
Budget1 | 3 | Name 3 | March | 5000 |
Budget1 | 4 | Name 4 | March | 5000 |
Budget1 | 5 | Name 5 | March | 5000 |
Budget1 | 1 | Name 1 | April | 5500 |
Budget1 | 2 | Name 2 | April | 5500 |
Budget1 | 3 | Name 3 | April | 5500 |
Budget1 | 4 | Name 4 | April | 5500 |
Budget1 | 5 | Name 5 | April | 5500 |
Budget1 | 1 | Name 1 | May | 6000 |
Budget1 | 2 | Name 2 | May | 6000 |
Budget1 | 3 | Name 3 | May | 6000 |
Budget1 | 4 | Name 4 | May | 6000 |
Budget1 | 5 | Name 5 | May | 6000 |
Budget1 | 1 | Name 1 | June | 6500 |
Budget1 | 2 | Name 2 | June | 6500 |
Budget1 | 3 | Name 3 | June | 6500 |
Budget1 | 4 | Name 4 | June | 6500 |
Budget1 | 5 | Name 5 | June | 6500 |
I also have some calculated columns in my model to insert a column for a month and year, then generate the date for each of these as the first of the relevant month (eg 1/7/2023 or 1/6/2024)
Based on this sample data, I would like to see these totals, the same as can be generated from excel:
Month | Cumulative | |
July | 5000 | 5000 |
August | 7500 | 12500 |
September | 10000 | 22500 |
October | 12500 | 35000 |
November | 15000 | 50000 |
December | 17500 | 67500 |
January | 20000 | 87500 |
February | 22500 | 110000 |
March | 25000 | 135000 |
April | 27500 | 162500 |
May | 30000 | 192500 |
June | 32500 | 225000 |
Thank you for your assistance
I managed to get the cumulative to work using this formula:
I have solved the calculation issues by adjusting the primary relationship to the date table to be active, but I now have 2 issues remaining:
1) If I make the date relationships active, the figuresare correct, but i lose connection to other sheets in the workbook because it has now made the other required relationship (Project ID) inactive. If I make the project id relationship active, the figures are incorrect.
Is there a way to make both calendar/date and the project ID field relationships active. Each of these fields exist in each of the ~10 data sheets I am working from and are required for slicer options to filter between views.
2) How do I change the cumulative and monthly calculations so the slicers work instead of calculating the total portfolio value regardless of what slicer is selected?
Thank you
Hi @kristi_in_heels,
Since you're using inactive relationship between 'Calendar' and 'Table', please modify the measures as below:
Forecast Value =
CALCULATE(
SUM('Table'[Baseline Forecast]),
USERELATIONSHIP('Calendar'[Date], 'Table'[Date]))
Cumulative Value =
var maxdate= MAX('Calendar'[Date])
return
CALCULATE(
SUM('Table'[Baseline Forecast]),
FILTER(
ALL('Calendar'),
'Calendar'[Date] <= maxdate),
USERELATIONSHIP('Calendar'[Date], 'Table'[Date]))
Thank you, this works perfectly.
EDIT - Below query solved. One of my calculated columns missed the correct year application so was throwing the dates out.
******
One question: when I hover over a certain month, one of the monthly figures doesn't show in the tooltip box. This is the system generated tooltip, not one I have created. The cumulative totals seem to still be correct, just some of the monthly figures drop out of the list of values. Would there be any reason for this?
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
88 | |
83 | |
76 | |
49 |
User | Count |
---|---|
145 | |
140 | |
109 | |
68 | |
55 |