Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Cumulative Total

Hi All,   I want to come up with a graph that shows the cumulative orders for years that has orders. For example,there were 2 orders in 1996 , 0 in 1997 and 1 order in 1998.  So ideally, on a grap...
Need Help
MartynRamsden's avatar
MartynRamsden
Solution Sage
6 years ago

Hi Anonymous 

 

I think I understand why that didn't work - are you using a date dimension table?

If so, try this:

 

Target = 
VAR MaxDate = MAX ( 'Calendar'[Date] )
VAR Result = 
CALCULATE (
    COUNTROWS( 'IB_SEAK HKM_HW' ),
    FILTER (
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MaxDate)
    )

RETURN 
Result

 

Best regards,

Martyn

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi MartynRamsden,

 

Thanks for the reply!!

I think I found the problem. I should use ALLEXCEPT('IB_SEAK HKM_HW','IB_SEAK HKM_HW'[Model]) instead of "ALL" since I have a filter applied on Model.

 



Thank you!

Joyce

  • MartynRamsden's avatar
    MartynRamsden
    Solution Sage
    6 years ago

    Excellent - glad you figured it out!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi MartynRamsden,

       

      Thanks for providing the previous formula! It helped me  a great deal. 

       

      However, I seem to have encountered another obstacle. 

      I used the formula that is listed below and when i look at the data on a yearly basis, the graphs that I have for the year and the model are accurate but the graph showing the cumulative number of models by year and sub-region is wrong.


      For example, in 2000, there were 8 machines and this is reflected correctly in the diagram showing the total number and the model since there are 5 HE and 3 LE (thus, 8 machines). However, this isnt reflected in the diagram which shows the number of machines by year and sub region, since the total number shown is only 5 (SEA NORTH) + 1 (SEA SOUTH) = 6. 

       

      CumulativeTotal = CALCULATE(SUM('IB_SEAK HKM_HW'[Index]),FILTER(ALLEXCEPT('IB_SEAK HKM_HW','IB_SEAK HKM_HW'[Sub-region],'IB_SEAK HKM_HW'[Model],'IB_SEAK HKM_HW'[Account: Country]),'IB_SEAK HKM_HW'[Date Installed]<=MAX('IB_SEAK HKM_HW'[Date Installed])))
       
      2
       
      This table above verifies that I do have 3 machines in SEA South and 5 machines in SEA North.  '
      1

       

      Do you know why this is happening? Is there any part of the measure that I am using that causes this problem? I've also applied some model filters and this can be seen on the extreme right hand side of the above screenshot.

       

      Thank you for your help!

       

      Best regards,

      JPY

      • MartynRamsden's avatar
        MartynRamsden
        Solution Sage
        6 years ago

        Anonymous 

         

        Are you able to share a copy of your pbix? Be sure to remove any sensitive before you do.