Forum Discussion

Kolumam's avatar
Kolumam
Icon for Post Prodigy rankPost Prodigy
6 years ago
Solved

Cumulative total based on the selected month

I have a date table and a Meter table. There exists a one to many relationship between them with a cross filter on both sides. I am trying to calculate a cumulative total of a column in the meter tab...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Kolumam ,

     

    First of all, we can create a spreated calculated table as the slicer, 

     

    SlicerTable = DISTINCT('Table'[Month])

     

    Then we can use a measure in visual filter to meet your requirement:

     

    Cumulative Size = CALCULATE(SUM('Table'[Size]), FILTER(ALLSELECTED('Table'),[Month]<=SELECTEDVALUE('Table'[Month])))

     

     


    Best regards,

     

Kolumam's avatar
Kolumam
Icon for Post Prodigy rankPost Prodigy
6 years ago

I can't share the pbix but the below is the output that I want:

 

Month           Size   Cumulative Size

Jan 2019        5        5

Feb 2019       6        11

March 2019   7        18

 

Now I have Month as the filter. When I filter Feb 2019 I wanted to get the below.

 

Month     Cumulative Size

Jan 2019  5

Feb 2019 11

v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Kolumam ,

 

First of all, we can create a spreated calculated table as the slicer, 

 

SlicerTable = DISTINCT('Table'[Month])

 

Then we can use a measure in visual filter to meet your requirement:

 

Cumulative Size = CALCULATE(SUM('Table'[Size]), FILTER(ALLSELECTED('Table'),[Month]<=SELECTEDVALUE('Table'[Month])))

 

 


Best regards,

 

  • Kolumam's avatar
    Kolumam
    Icon for Post Prodigy rankPost Prodigy
    6 years ago

    can you share the pbix? v-lid-msft 

    • v-lid-msft's avatar
      v-lid-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi Kolumam ,

       

      Please download the sample pbix file at the end of our previous reply.

       


      Best regards,

       

    • v-lid-msft's avatar
      v-lid-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi Kolumam ,

       

      How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


      Best regards,

       

  • Anthony_G1's avatar
    Anthony_G1
    Frequent Visitor
    2 years ago

    Hello,

     

    I need to do something similar to the original poster, except display the Cumulative Size in a Card visual. Downloading your pbix, if I drag the Cumulative Size measure into a Card visual, it displays as (Blank). Is there a way to do this? Thank you!