Forum Discussion

tragonneau's avatar
tragonneau
Frequent Visitor
9 years ago
Solved

Problem with cumulation total when adding filters

Hello,   I have the following table : I have created this measure to have the cumulative total of the Qty : qty_cumul = CALCULATE( SUM(Stock[Qty]); FILTER( ALL(Stock[date]); Stock[d...
Need Help
  • blopez11's avatar
    blopez11
    9 years ago

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

     

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

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

 

 

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

  • tragonneau's avatar
    tragonneau
    Frequent Visitor
    9 years ago

    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.