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...
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)
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

Seward12533's avatar
Seward12533
Solution Sage
8 years ago
Rather than have the the week number come from your fact/data table have use the week number from the date table.

The **bleep** Measure would be following this pattern.

**bleep** = CALCULATE(sum(fact[value],all(date),filter(date,date[date]<=max(date[date]))

In your case since you only want cume YTD you would need to add a condition to filter out prior and future years &&date[year]=year(now())

So it would look something like
**bleep** = CALCULATE(sum(fact[value],all(date),filter(date,date[date]<=max(date[date]&&date[year]=year(now()))

The problem your having is your slicers are eliminating the data so your current measure for calculating the **bleep** will return null so nothing is displayed the measure based on the dates from the date table will return the results you require and you let the filter context of the PowerBI engine do the work an you don’t need to deal with the secondary table or the complexities of earlier. (Which is awesome that you figured out I still don’t have my head fully around that. )