Forum Discussion

viniciusfigueir's avatar
viniciusfigueir
Frequent Visitor
6 years ago
Solved

Running Total with Slicer

I'd like to create a running total where I can filter the rows used by using a date slicer on another column. I have one data table with two columns, 'time' and 'dat', that looks like this:
table_a

timedat
729/07/2019 13:33
1502/01/2018 15:09
6003/01/2018 15:53
2530/07/2019 14:16
604/01/2018 15:11
831/07/2019 13:09
3101/08/2019 13:25
1402/08/2019 12:00
2911/01/2018 11:00
2005/08/2019 13:56
3006/08/2019 13:59
2007/08/2019 12:04
1907/08/2019 12:05
13708/08/2019 13:58
13208/08/2019 14:00
2009/08/2019 14:22
2009/08/2019 14:24
1322/01/2018 13:31
812/08/2019 15:43
712/08/2019 15:44
6423/01/2018 11:50

 

To achieve a running total area chart I've created a new table, table_b:

 
table_b = GENERATESERIES(1;70)      (column name: 'value')
and its columns:
count = CALCULATE(COUNTROWS(table_a); FILTER(ALLSELECTED(table_a); table_a[time] <= 'table_b'[value]))
relative = 'table_b'[count] / MAX('table_b'[count])
 
However, as noted by the picture, a date slicer on column 'dat' does not affect my Area chart. How can I make the slicer affect the chart?
GENERATESERIES(1;70) should still be GENERATESERIES(1;70), but I'd like count to use only the subset of the rows selected by the date slicer.
 
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi viniciusfigueir 

    First, you should know that:

    1. Calculation column/table not support dynamic changed based on filter or slicer.
    2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

    https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

    Second, Try these two measure that based on your column formula

    Count maeasure = CALCULATE(COUNTROWS(table_a), FILTER(ALLSELECTED(table_a), table_a[time] <= MAX(table_b[Value])))
    relative measure = [Count maeasure]/CALCULATE(MAXX(table_b,[Count maeasure]),ALLSELECTED(table_b))

     

    Regards,

    Lin

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    You will need to use a measure then, not a column. Columns can't be dynamic like that.

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

        hi viniciusfigueir 

        First, you should know that:

        1. Calculation column/table not support dynamic changed based on filter or slicer.
        2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

        https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

        Second, Try these two measure that based on your column formula

        Count maeasure = CALCULATE(COUNTROWS(table_a), FILTER(ALLSELECTED(table_a), table_a[time] <= MAX(table_b[Value])))
        relative measure = [Count maeasure]/CALCULATE(MAXX(table_b,[Count maeasure]),ALLSELECTED(table_b))

         

        Regards,

        Lin