Forum Discussion

rfernandez's avatar
rfernandez
Regular Visitor
7 years ago
Solved

Cumulative graph

Hi, I have this data  Type PoR Amount Month A Real 5 1 A Real 4 2 A Real 5 3 A Real 8 4 A Real 9 5 A Plan 2 1 A Plan 3 2 A Plan 6 3 A Plan ...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi rfernandez,

     

    You can achieve this by creating the following measures:

     

    Real-Plan = 
CALCULATE ( SUM ( Plan[Amount] ); Plan[PoR] = "Real" )
    - CALCULATE ( SUM ( Plan[Amount] ); Plan[PoR] = "Plan" )




Cumulative =
CALCULATE (
    SUMX ( Plan; [Real-Plan] );
    FILTER ( ALLSELECTED ( Plan[Month] ); Plan[Month] <= MAX ( Plan[Month] ) )
)

    Then just use your cumulative measure on your charts.

     

    Although the ansewer by AnkitBI is good, a good practice is to create a measure instead of calculated columns since they add size to your model and complexity.

     

    This option works both for PBI and for Excel PowerPivot see attach both files with the solutions.

     

    Regards,

    MFelix

     

     

MFelix's avatar
MFelix
Super User
7 years ago

Hi rfernandez,

 

You can achieve this by creating the following measures:

 

Real-Plan = 
CALCULATE ( SUM ( Plan[Amount] ); Plan[PoR] = "Real" )
    - CALCULATE ( SUM ( Plan[Amount] ); Plan[PoR] = "Plan" )




Cumulative =
CALCULATE (
    SUMX ( Plan; [Real-Plan] );
    FILTER ( ALLSELECTED ( Plan[Month] ); Plan[Month] <= MAX ( Plan[Month] ) )
)

Then just use your cumulative measure on your charts.

 

Although the ansewer by AnkitBI is good, a good practice is to create a measure instead of calculated columns since they add size to your model and complexity.

 

This option works both for PBI and for Excel PowerPivot see attach both files with the solutions.

 

Regards,

MFelix