Forum Discussion

kristi_in_heels's avatar
kristi_in_heels
Icon for Helper II rankHelper II
3 years ago
Solved

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 mont...
Need Help
  • isjoycewang's avatar
    isjoycewang
    3 years ago

    Hi kristi_in_heels,

     

    Exercise File

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

     

kristi_in_heels's avatar
kristi_in_heels
Icon for Helper II rankHelper II
3 years ago

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

 

isjoycewang's avatar
isjoycewang
Icon for Solution Supplier rankSolution Supplier
3 years ago

Hi kristi_in_heels,

 

Exercise File

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

 

  • kristi_in_heels's avatar
    kristi_in_heels
    Icon for Helper II rankHelper II
    2 years ago

    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?