cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tragonneau Frequent Visitor
Frequent Visitor

Problem with cumulation total when adding filters

Hello,

 

I have the following table :

tableScreen.pngStockTable

I have created this measure to have the cumulative total of the Qty :

qty_cumul = 
CALCULATE( SUM(Stock[Qty]); FILTER( ALL(Stock[date]); Stock[date] <= MAX(Stock[date]) ) )

 

It works as I want. When I want to see the evolution of the cumulative total for specific entities (let's say ENT_1 and ENT_2), it still works well. 

 

HOWEVER i have a problem when I add a visual filter to see the evolution of the total for a specific period. The result is not the sum of the cumulative quantity of ENT_1 and ENT_2 (whereas it is when i don't use the filter on the date).

 

cumulativeTotalProblemWithDateFilter.png

 

I realised this graph is displaying alternatively the cumulative total of ENT_1 and ENT_2 which is not what I want.

When I added the filter on the date i expected my bottom chart to be a "zoom in" of the top chart.

 

I don't understand why it doesn't work as I want. I feel like I am missing something.

Can you help me please.

 

I am working in DirectQuery mode but to explain my issue i created a custom table.

Here the file i used to do the screenshots:

https://www.dropbox.com/s/sekh8g8ht03ibbs/StockCumulativeTotal.pbix?dl=0

1 ACCEPTED SOLUTION

Accepted Solutions
blopez11 Established Member
Established Member

Re: Problem with cumulation total when adding filters

Something similar to the following worked for me, but I needed a date table, where on my visual I used the month field from the date table for the axis, and I had a slicer that used year from the date table.  However, I haven't tried in DirectQuery mode

 

Cumulative Quantity :=
CALCULATE (
    SUM ( Transactions[Quantity] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

 

View solution in original post

3 REPLIES 3
blopez11 Established Member
Established Member

Re: Problem with cumulation total when adding filters

Something similar to the following worked for me, but I needed a date table, where on my visual I used the month field from the date table for the axis, and I had a slicer that used year from the date table.  However, I haven't tried in DirectQuery mode

 

Cumulative Quantity :=
CALCULATE (
    SUM ( Transactions[Quantity] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

 

View solution in original post

Moderator v-qiuyu-msft
Moderator

Re: Problem with cumulation total when adding filters

Hi @tragonneau,

 

From the .PBIX file you provided, you have added filter for the bottom line chart in Visual Level Filter property, which only apply to the bottom line chart. This filter setting will not apply for other line chart or table visual. So the data for the same date display as different values in those two line charts. See: Add a filter to a Power BI report (in Editing view).

 

a2.PNG

 

In your scenario, please remove visual level filter, add the same filter on page level filter, this filter will apply for all visuals in this report page. Also you need to apply the filter like below:

 

qty_cumul = CALCULATE(
    SUM(Stock[Qty]),
    FILTER(
     ALLSELECTED(Stock),
     Stock[date] <= MAX(Stock[date])
    )
)

 

Please download and run the attached .PBIX file to see if it meet your requirement.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
tragonneau Frequent Visitor
Frequent Visitor

Re: Problem with cumulation total when adding filters

@blopez11

Thanks, it works well with the DateTable. I don't know why i didn't use it.

 

@v-qiuyu-msft

Thanks, but it's not what i wanted to do.

My explanations were confusing, i'm sorry.

Maybe it will make more sense with the following screenshot.

I wanted to see the values for a shorter period : the top side chart represents the values in the red rectangle.

solutionWanted.png

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)