Forum Discussion

JLambs20's avatar
JLambs20
Icon for Helper III rankHelper III
5 years ago

Cumulative Sum based on column entries

Hello! I have a table of information that i'll show in Excel in order to understand it better.  Essentially, I need to get a cumulative sum, in a table, of time saved based on a current version (along with all included versions).  In other words, if we are looking at version 2, i'd need to summarize the time saved between Version 1 and Version 2. We know which versions are included in a current version based on manual input from a user (if they are looking at version 3 currently, they'd select version 1, 2, and 3).  In the end I'd like a Matrix table that shows the version, versions included, then a sum of all of the time saved. Something to keep in mind is that these entries will not be sequential so we can't just look "backwards" for the items prior to the current version. We'd need to look at the "versions included" column. 

 

Data Table example:

Matrix Table Result:

 

 

 

 

 

 

 

 

 

 

 

 

Should this be done in Power Query or can it be pulled off in DAX? 

 

Thanks in advance everyone!

Need Help

4 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    Hi JLambs20 ,

     

    Create a disconnected table with the versions.

    Add the following measure to your model:

    Timesaved =
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[Version],
        "@TimeSaved",
            IF (
                SELECTEDVALUE ( 'Table'[Version] ) <= SELECTEDVALUE ( Versions[Version] ),
                SUM ( 'Table'[Time Saved] )
            )
    ),
    [@TimeSaved]
)

     

    Create the matrix in the following way:

    • Rows:
      • Version[Version]
      • TimeSavedTable[Version]
    • Values
      • Timessaved (measure)

     

     

     

    Now create the 

    • JLambs20's avatar
      JLambs20
      Icon for Helper III rankHelper III
      5 years ago

      Hello Miguel and thank you for taking the time to respond. Unfortunately, your solution didn't quite get me there.  I created the version table and the measure, but my values were not displaying as I need them to.  I'm going to add some more screenshots to show you more specifically what I needed. This first one is an extract of my data table in BI (exported to Excel) just to show you exactly what I'm working with.  The whole idea is for an end user to be able to select a version from a slicer and then show that version along with all of the versions included in that one (which are manually selected in the original raw data) in a table with total hours saved per version (with the total rolling up).  Here is the extract:

      Here is 

      Here is the resulting Matrix in BI:

       

      As you can see, I also have the issue of getting each of those versions onto their own row to show individual summary hours. 

       

      Again I appreciate all of your help. 

       

      Regards,

      Josh

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        5 years ago

        Hi JLambs20 ,

         

        Don't think you are using the correct format in the matrix.

         

        Did you create the disconnected table with all the Versions ID? To what I can see you are using the version and the Included in Version columns on your model.

         

        Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

        If the information is sensitive please share it trough private message.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi JLambs20 

    Has your problem been solved ? If no, please provide your sample and the result you want .

    If it has been solved, provide your method for the problem then consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards

    Community Support Team _ Ailsa Tao