Cumulative total all ignore specific filter help

[Royston]

Hi everyone,

Long time stalker, first time poster - so thank you for all your help previously.

I've been struggling with time series data for a while now. Usually googling and youtubing eventually helped but i'm really stuck on this one even though it seems simple enough. I might not be getting my concepts right, so appreciate if any gurus can enlighten me as well. I have attached the data in excel here.

I have a list of processed goods in tonnes by hour, day and operator that I want to display the cumulative processed goods by hour.

I have managed to get the cumulative total to work when the hours are selected in a slicer with this code:

CALCULATE(
        SUM('table'[Processed (t)])
            ,FILTER(
                    ALLSELECTED('table'[End At Hour (Local)])
                        ,'table'[End At Hour (Local)] <= MAX('table'[End At Hour (Local)]))
            )

However, how can i get it to work when i want the first value to display the cumulative data from the hour prior. I googled and tested various methods but couldn't get it to work somehow. I konw i'm close and i'm missing something important.

I landed on 2 different methods that produced results:

1) using the 'ALL' function

2) using the 'ALLEXCEPT' function

However, Method 1 returned the daily total for the selected operator in each row, whilst Method 2 returned the daily total in each row.

Method 1 code:

CALCULATE(
        SUM('table'[Payload (t)])
            ,FILTER(
                    ALL('table'[End At Hour (Local)])
                        ,'table'[End At Hour (Local)] <= MAX('table'[End_At_Local]))
            )

Method 2 code:

CALCULATE(
        SUM('table'[Payload (t)])
            ,ALLEXCEPT('table','table'[End At Hour (Local)])
            ,'table'[End At Hour (Local)] <= MAX('table'[End_At_Local])
            )

Ideally i'd like it the graph to look like - 

Thank you very much.

Best,

Roy

[v-ssriganesh]

Hello @Royston,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[v-ssriganesh]

Hello @Royston,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[Royston]

Hi Ganesh,

Thanks for the follow up, I missed this message as I was on my break. The graph you produced was not what I was looking for, unfortunately. 

I was hoping to have the red line remain static regardless of what hour filter was chosen. So for example, assuming hour 4's value is 50 but the cumulative value is 1000 when counted from 0, I'd like the graph to start at 1000 when i select the hour 4 on the slicer instead of 50.

Does that make sense?

Thanks.

Best,

Roy

[v-ssriganesh · Accepted solution]

Hello @Royston,

Thank you for clarifying your requirement further. I have updated the .pbix file to ensure the red cumulative line remains static, regardless of the hour selected in the slicer.

For example, with your sample data:

• When only Hour 3 is selected, the blue cumulative shows 288 (local to filter), but the red cumulative shows 1044 (total up to Hour 3, ignoring the slicer).
• When only Hour 4 is selected, the blue cumulative shows 289, and the red cumulative correctly shows 1333.

This matches your expectation that the red line continues from the prior cumulative value rather than restarting at the slicer.

I am attaching the updated .pbix file for your reference so you can test it with your own data.

Best regards,
Ganesh Singamshetty

[Royston]

Hi Ganesh,

Thank you very much, it worked! 

Can you explain how it works please? I understand it is based on the row filter context but i'm still trying to wrap my head around it. My Method 1 looks very similar to your code but i'm wondering what's different?

Thank you.

Best,

Roy

[v-ssriganesh]

Hello @Royston.
Glad it worked. The key difference is in how the filter context is handled:

• In your version, the slicer on End At Hour (Local) limited the calculation, so when you picked Hour 4, only that row (289) was visible.
• In my version, I used ALL('Table'[End At Hour (Local)]) to ignore the slicer, then reapplied a filter with
• 'Table'[End At Hour (Local)] <= MAX ( 'Table'[End At Hour (Local)] )

This way, the measure always sums from 0 selected hour, even if the slicer shows only one.

Best regards,
Ganesh Singamshetty.

[v-ssriganesh]

Hello @Royston,
Thank you for reaching out to the Microsoft fabric community forum. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.

I am also including .pbix file for your better understanding, please have a look into it:

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Fabric Community Forum.

Best regards,
Ganesh Singamshetty.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Royston]

I can't attach any file as i'm a new user.

Is there a way around this please?

thanks.

[pankajnamekar25]

Create dummy data in your personal computer and share file

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.


Thanks,


Connect with me on:

LinkedIn

[pankajnamekar25]

Hello @Royston 

Try This DAX

Cumulative Processed (t) =
VAR CurrentHour = MAX('table'[End At Hour (Local)])
RETURN
CALCULATE (
SUM ( 'table'[Processed (t)] ),
FILTER (
ALLEXCEPT ( 'table', 'table'[Operator], 'table'[Date] ),
'table'[End At Hour (Local)] <= CurrentHour
&& 'table'[End At Hour (Local)] >= CurrentHour - 1
)
)

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.


Thanks,


Connect with me on:

LinkedIn