Forum Discussion

unnijoy's avatar
unnijoy
Post Prodigy
7 years ago
Solved

Find sum based on filter

I have two column Month and Status (1 or 0). In month i have month from Jan 2001 to June 2019. I have a month slicer. I need help in creating a formula in sucha way that when i select a particular mo...
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    7 years ago

    Hi unnijoy ,

     

    By my test, you could create a irrelative table as a slicer to filter the values. I create a sample using two ways you can have a try.

     

    1. Create a new table. And the values I added is the same as your sample data. There is no relationship between two tables.
    2. Create measures.

    Method 1:

    Note : it will show the original values and the expected total.

    Measure =
var a = SELECTEDVALUE(Table2[Month ])
return CALCULATE(SUM(Table1[Status]),FILTER(Table1,MAX(Table1[Month ])<=a))

    Measure 2 = IF(ISFILTERED(Table2[Month ]),CALCULATE(SUMX(Table1,Table1[Measure]),ALLEXCEPT(Table1,Table1[Month ])),SUM(Table1[Status]))

    Method 2:

    Note: It will show blank by default.

    Measure 3 = IF(ISFILTERED(Table2[Month ]) && MAX(Table1[Month ])<=SELECTEDVALUE(Table2[Month ] ),SUMX(FILTER(ALL(Table1),'Table1'[Month ] <=SELECTEDVALUE(Table2[Month ])),Table1[Status]))

    Best Regards,

    Xue Ding 

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

v-xuding-msft's avatar
v-xuding-msft
Community Support
7 years ago

Hi unnijoy ,

 

By my test, you could create a irrelative table as a slicer to filter the values. I create a sample using two ways you can have a try.

 

  1. Create a new table. And the values I added is the same as your sample data. There is no relationship between two tables.
  2. Create measures.

Method 1:

Note : it will show the original values and the expected total.

Measure =
var a = SELECTEDVALUE(Table2[Month ])
return CALCULATE(SUM(Table1[Status]),FILTER(Table1,MAX(Table1[Month ])<=a))

Measure 2 = IF(ISFILTERED(Table2[Month ]),CALCULATE(SUMX(Table1,Table1[Measure]),ALLEXCEPT(Table1,Table1[Month ])),SUM(Table1[Status]))

Method 2:

Note: It will show blank by default.

Measure 3 = IF(ISFILTERED(Table2[Month ]) && MAX(Table1[Month ])<=SELECTEDVALUE(Table2[Month ] ),SUMX(FILTER(ALL(Table1),'Table1'[Month ] <=SELECTEDVALUE(Table2[Month ])),Table1[Status]))

Best Regards,

Xue Ding 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.