Forum Discussion

kristi_in_heels's avatar
kristi_in_heels
Helper II
2 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
    2 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]))

     

isjoycewang's avatar
isjoycewang
Solution Supplier
2 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
Helper 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?