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

Help with Cumulative Totals

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)

 

kristi_in_heels_0-1694064050713.png

 

 

1 ACCEPTED SOLUTION

Hi @kristi_in_heels,

 

Exercise File

Since you're using inactive relationship between 'Calendar' and 'Table', please modify the measures as below:

isjoycewang_2-1694156973727.png

 

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]))

isjoycewang_3-1694157036691.png

 

View solution in original post

6 REPLIES 6
isjoycewang
Super User
Super User

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

 

isjoycewang_0-1694071960425.png

 

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.

 

kristi_in_heels_0-1694124957491.png

 

 

Sample data is below for reference:

 

PeriodProject DefinitionProjectMonthBaseline Forecast
Budget11Name 1July1000
Budget12Name 2July1000
Budget13Name 3July1000
Budget14Name 4July1000
Budget15Name 5July1000
Budget11Name 1August1500
Budget12Name 2August1500
Budget13Name 3August1500
Budget14Name 4August1500
Budget15Name 5August1500
Budget11Name 1September2000
Budget12Name 2September2000
Budget13Name 3September2000
Budget14Name 4September2000
Budget15Name 5September2000
Budget11Name 1October2500
Budget12Name 2October2500
Budget13Name 3October2500
Budget14Name 4October2500
Budget15Name 5October2500
Budget11Name 1November3000
Budget12Name 2November3000
Budget13Name 3November3000
Budget14Name 4November3000
Budget15Name 5November3000
Budget11Name 1December3500
Budget12Name 2December3500
Budget13Name 3December3500
Budget14Name 4December3500
Budget15Name 5December3500
Budget11Name 1January4000
Budget12Name 2January4000
Budget13Name 3January4000
Budget14Name 4January4000
Budget15Name 5January4000
Budget11Name 1February4500
Budget12Name 2February4500
Budget13Name 3February4500
Budget14Name 4February4500
Budget15Name 5February4500
Budget11Name 1March5000
Budget12Name 2March5000
Budget13Name 3March5000
Budget14Name 4March5000
Budget15Name 5March5000
Budget11Name 1April5500
Budget12Name 2April5500
Budget13Name 3April5500
Budget14Name 4April5500
Budget15Name 5April5500
Budget11Name 1May6000
Budget12Name 2May6000
Budget13Name 3May6000
Budget14Name 4May6000
Budget15Name 5May6000
Budget11Name 1June6500
Budget12Name 2June6500
Budget13Name 3June6500
Budget14Name 4June6500
Budget15Name 5June6500

 

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)

kristi_in_heels_1-1694124980432.png

Based on this sample data, I would like to see these totals, the same as can be generated from excel:

 

 MonthCumulative
July50005000
August750012500
September1000022500
October1250035000
November1500050000
December1750067500
January2000087500
February22500110000
March25000135000
April27500162500
May30000192500
June32500225000

 

kristi_in_heels_2-1694125079348.png

 

Thank you for your assistance

I managed to get the cumulative to work using this formula:

 

Cumulative = CALCULATEsum('Table'[Baseline Forecast]), filter(all('Table'), 'Table'[Date] <=max('Calendar'[Date])))
 
But this is not changing when I adjust slicers, the total is remaining as the total for all the data. I need this to change based on slicer selection. I have checked relationships and all are correct.
 
My monthly individual totals are also still not calculating correctly.
 
Any ideas on how to correctly sum by individual month? I am sure this is something simple that I am missing!
 
Thank you

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,

 

Exercise File

Since you're using inactive relationship between 'Calendar' and 'Table', please modify the measures as below:

isjoycewang_2-1694156973727.png

 

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]))

isjoycewang_3-1694157036691.png

 

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?

 

kristi_in_heels_0-1694386382973.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors