Forum Discussion

Ed212's avatar
Ed212
Regular Visitor
1 year ago
Solved

Changing the filter context for calculating cummulative values

Hi there, I'm not an expert of PowerBI, far from it. But I've been researching a lot how can I solve this problem (learning about row and filter context in measures, etc.), and I'm start becoming de...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from lbendlin , please allow me to provide another insight:

    Hi, Ed212 

     

    According to our security protocols, I am currently unable to download the latest PBIX sample data you provided. However, I have obtained the sample data provided by lbendlin .

     

    Regarding the issue you raised, my solution is as follows:

    1.Considering your need to use a slicer, I created the following calculated column to perform cumulative addition:

    run1 = 
VAR cy = 'example'[YearNumber]
VAR cw = 'example'[WeekNumber]
VAR aa2 =
    CALCULATE (
        SUM ( 'example'[Quantity Sold] ),
        FILTER (
            ALLSELECTED ( 'example' ),
            'example'[YearNumber] = cy - 1
                && 'example'[Product] = EARLIER ( 'example'[Product] )
        )
    )
VAR aa3 =
    IF (
        'example'[WeekNumber] = MIN ( 'example'[WeekNumber] ),
        'example'[Quantity Sold] + aa2,
        'example'[Quantity Sold]
    )
RETURN
    aa3

    run2 = 
CALCULATE (
    SUM ( 'example'[run1] ),
    FILTER (
        ALLSELECTED ( 'example' ),
        'example'[Product] = EARLIER ( 'example'[Product] )
            && 'example'[YearNumber] = EARLIER ( example[YearNumber] )
            && 'example'[WeekNumber] <= EARLIER ( example[WeekNumber] )
    )
)

    2.Next, I used the following measure to control the display content:

    MEASURE = 
IF (
    ISINSCOPE ( 'example'[YearNumber] ),
    IF (
        ISINSCOPE ( example[WeekNumber] ),
        SUM ( 'example'[run2] ),
        SUM ( 'example'[run1] )
    ),
    CALCULATE (
        SUM ( example[run1] ),
        FILTER (
            ALLSELECTED ( 'example' ),
            'example'[YearNumber] = MAX ( 'example'[YearNumber] )
        )
    )
)

    3.Here's my final result, which I hope meets your requirements.

     

    4.For questions about uploading data, you can try the following links:

    How to provide sample data in the Power BI Forum - Microsoft Fabric Community

    Solved: How to upload PBI in Community - Microsoft Fabric Community

     

    Please find the attached pbix relevant to the case.

     

    Best Regards,

    Leroy Lu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ed212's avatar
Ed212
Regular Visitor
1 year ago

I have edited the original post, and now it includes the files.

lbendlin's avatar
lbendlin
Super User
1 year ago

You need to use a proper calendar table if you want to use that slicer. (Although I would say the slicer is not actually required)

 

Keep in mind that the Quick Measures offer a "Running Total"  pattern - you should use that as a start.

 

 

  • Ed212's avatar
    Ed212
    Regular Visitor
    1 year ago

    Thank you lbendlin for your help! Unfortunately this is not what I'm looking for. I have tried every combination of quickmeasures I could think of, but no success either. I have worked further with my example (date table and so on). The updated file:
    ExampleReport4.pbix 
    Let me explain:

    The main purpose of the calculation is to create a graph, showing the evolution of the cummulated value. Lets forget the products for a while. The result from the measure should be this (in red):

    It should mantain the calculation of the cummulated value even when I change the year. It would allow me to slice the year using the slicer and "continue" with the cummulated values. Visually like this (despite the values are not right):

    As you can see, if we select 2024 the first week doesn't start the cummulated value from 0. It takes the subtotal from the past years (in this case, 2023).

    Any idea how can I do this?

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Thanks for the reply from lbendlin , please allow me to provide another insight:

      Hi, Ed212 

       

      According to our security protocols, I am currently unable to download the latest PBIX sample data you provided. However, I have obtained the sample data provided by lbendlin .

       

      Regarding the issue you raised, my solution is as follows:

      1.Considering your need to use a slicer, I created the following calculated column to perform cumulative addition:

      run1 = 
VAR cy = 'example'[YearNumber]
VAR cw = 'example'[WeekNumber]
VAR aa2 =
    CALCULATE (
        SUM ( 'example'[Quantity Sold] ),
        FILTER (
            ALLSELECTED ( 'example' ),
            'example'[YearNumber] = cy - 1
                && 'example'[Product] = EARLIER ( 'example'[Product] )
        )
    )
VAR aa3 =
    IF (
        'example'[WeekNumber] = MIN ( 'example'[WeekNumber] ),
        'example'[Quantity Sold] + aa2,
        'example'[Quantity Sold]
    )
RETURN
    aa3

      run2 = 
CALCULATE (
    SUM ( 'example'[run1] ),
    FILTER (
        ALLSELECTED ( 'example' ),
        'example'[Product] = EARLIER ( 'example'[Product] )
            && 'example'[YearNumber] = EARLIER ( example[YearNumber] )
            && 'example'[WeekNumber] <= EARLIER ( example[WeekNumber] )
    )
)

      2.Next, I used the following measure to control the display content:

      MEASURE = 
IF (
    ISINSCOPE ( 'example'[YearNumber] ),
    IF (
        ISINSCOPE ( example[WeekNumber] ),
        SUM ( 'example'[run2] ),
        SUM ( 'example'[run1] )
    ),
    CALCULATE (
        SUM ( example[run1] ),
        FILTER (
            ALLSELECTED ( 'example' ),
            'example'[YearNumber] = MAX ( 'example'[YearNumber] )
        )
    )
)

      3.Here's my final result, which I hope meets your requirements.

       

      4.For questions about uploading data, you can try the following links:

      How to provide sample data in the Power BI Forum - Microsoft Fabric Community

      Solved: How to upload PBI in Community - Microsoft Fabric Community

       

      Please find the attached pbix relevant to the case.

       

      Best Regards,

      Leroy Lu

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Ed212's avatar
        Ed212
        Regular Visitor
        1 year ago

        Anonymous , it is exactly what I was looking for. Thanks a lot!!