Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Visual Filter for Cumulative Sum Issue

Hi,

 

I need some help with cumulative sum line.

 

Before selecting any slicer, the visual shows this:

But after applying filter, the visual shows the below.

How then can I make the line graph show the full graph, starting from zero to the end of the chart, similar to the first screenshot?

 

Regards,
Nicholas Hiew

Need Help
  • parry2k's avatar
    parry2k
    8 years ago

    AnonymousUpdate cummulative total DAX as give below and that will do it, as Seward12533 suggested to use cross filter to both direction, it has performance hit but instead of setting up the relationship, you can use it in DAX formula basically you are using cross filter to both direction when required

     

    Cumulative = 
CALCULATE(
SUM('Table2'[Count]),
CROSSFILTER(Table1[Week of Year],Table2[Week No.], Both),
FILTER(ALL('Table2'),
'Table2'[Week No.]<=MAX('Table2'[Week No.]))
)
  • Seward12533's avatar
    Seward12533
    8 years ago

    Anonymous try this

    Cumulative = VAR Result = 
CALCULATE(
SUM('Table2'[Count]),
CROSSFILTER(Table1[Week of Year],Table2[Week No.], Both),
FILTER(ALL('Table2'),
'Table2'[Week No.]<=MAX('Table2'[Week No.]))
)
RETURN
IF(Result,Result,0)

17 Replies

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

    Can you share sample data and what is your current measure?

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Both,

       

      This is my desired result (lines in red), when sliced by category.

       

      My Data (Table 1) looks like this, with the last column "Week No." is created in query editor:

       

      And I have another table (Table 2) created to give me the following value (only the first column is from an excel sheet):

      For cumulative column (note that I use excel to replicate these), I used the following measure:

      Cumulative =
      CALCULATE(
      SUM('Table 2'[Count]),
      FILTER(ALL('Table 2'),
      'Table 2'[Week No.]<=MAX('Table 2'[Week No.]))
      )

       

      EDIT: I also tried adding a column for cumulative, which still yield the same visual results:

      Cumulative =
      CALCULATE(
      SUM('Table 2'[Count]),
      FILTER(ALL('Table 2'),
      'Table 2'[Week No.]<=EARLIER('Table 2'[Week No.]))
      )

       

      Appreciate your help. Thanks.

       

      Regards,
      Nicholas

      • parry2k's avatar
        parry2k
        Super User
        8 years ago

        Hey Anonymous Can you share the data in excel sheet for the solution?

  • Seward12533's avatar
    Seward12533
    Solution Sage
    8 years ago

    Also what is your desired result? do you want the **bleep** to be unaffected by the filter or do you want the scale to be fixed?  The problem is the **bleep** is being plotted on its own axis and PBI is dynamically adjusting it based on the data. Or do you want the other data to not be displayed and only show the filtered data?

     

    If you want the **bleep** to adjust.

    Look at either turning off the secondary Y axis to you **bleep** is plotted on the same scale (probably not what you want) or fixing the scales manually

     

    If you want the **bleep** not be affected make sure you the USE the ALL filter(s) for the slicers your using

     

    If you want the other data to be filtered out then adjust the visual interations and chose filter.