Forum Discussion

JurriaanQEL's avatar
JurriaanQEL
Regular Visitor
8 years ago
Solved

Remove cumulative total

Hi,    Every month I receive a file with updated numbers. One of the columns is cumulative, e.g.: January     -    20 February   -    25 March       -    40   I want to have a table in Power ...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi JurriaanQEL

     

    Please see the attached file here.

    Hope this helps. Here are the steps I performed

     

    As you said we need an assistant field to undo the Cumulatives. So
    First I created a "Parameter Table" to get "month number" in our Main Table

    Then we can use this Calculated Column to get Monthly figures

    Monthly Figure =
VAR PreviousMonthValue =
    CALCULATE (
        SUM ( Table1[Cumulative] ),
        FILTER (
            Table1,
            Table1[Year] = EARLIER ( Table1[Year] )
                && Table1[MonthNumber]
                    = EARLIER ( Table1[MonthNumber] ) - 1
        )
    )
RETURN
    Table1[Cumulative] - PreviousMonthValue




     

     

     

     

     

JurriaanQEL's avatar
JurriaanQEL
Regular Visitor
8 years ago

Hi again Zubair_Muhammad

 

After a frustrating few tries, I have to admit defeat :(

I didn't manage to make it work in my workbook.

I have the fields Month, Year and Monthname already in my dim_date table, so I didn't need to create a separate table for this (I think?). 

So I changed your DAX to fit my workbook, which you can see below.

Your cumulative field is called (CA) Indemnity Paid (This Month) for me. 

 

I first had v_f_Bordereau_line[(CA) Indemnity Paid (This Month)] in the DAX, but that gave me this error:

A single value for column '(CA) Indemnity Paid (This Month)' in table 'v_f_Bordereau_line' cannot be determined.

Then I changed it to the sum(v_f_Bordereau_line[(CA) Indemnity Paid (This Month)]), which results in what you can see below.

You can see in the second table (on the right) that the total is 20,032,538.51, but that from March 2016 this is gradually growing (and is the cumulative part). Why I don't get the same in the left table is beyond me. When removing the Sum (Don't Summarize) it will give me the error 'Can't determine relationships between the fields'... so yeah, I am very lost!

Any thoughts??

 

 

Thanks in advance!

Kind regards, Jurriaan

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago
Hi Jurriaan
Could you upload your file to one drive or googledrive
And share link here

  • JurriaanQEL's avatar
    JurriaanQEL
    Regular Visitor
    8 years ago

    Hi Zubair,

     

    Thanks for the quick reply.

    I've uploaded it, after stripping out some fields, data, etc (business information....)

    https://1drv.ms/u/s!AiecK9GUkh9Aqyx8cBitsE4kZgW4

    I hope I didn't remove too much.. I've left the fields I think should be there.

     

    Thanks again.

     

    Kind regards,

    Jurriaan

     

    Ps. I took a look at your website... nice stuff!

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago
      Hi
      I will get my hands on laptop in 2 to 3 hours
      Replying from my phone

      I will do my best
      • JurriaanQEL's avatar
        JurriaanQEL
        Regular Visitor
        8 years ago

        No rush, I am calling it a day soon anyway and I am more than grateful for you giving it a try anyway!