multi level filtering

[Martynas_BI]

Hi,

I have the following table in Power BI report view, which is affected by a date filter

I would like to be able to create a measure that would calculate the sum of the inventory for a particular region respecting the report page date filter (I have added the expected output I would like to get below to make it clearer)

I have been playing around with various filters, it seems to be relatively simple but complex topic at the same time for me.

The sample dataset could be found here:
https://docs.google.com/spreadsheets/d/18oXErJLYF_ExELsBvznenUIO2s3Qs_FRkapCEEB0jCA/edit#gid=0

Any advice would be highly appreciated.

Many thanks,

[Ritaf1983]

Hi @Martynas_BI 
Allexcept is the function that worked perfectly:

sum per region = calculate (sum('Table'[Inventory]),ALLEXCEPT('Table','Table'[Region]))

Link to a sample file 

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

[Martynas_BI]

@Ritaf1983 

thanks for the suggestion, unfortunately that's not it. I have used this formula in my sample snippet, however, it's not exactly what I am looking for.

Please note that I have a larger dataset (I have added a few more months of data to the sample data set, maybe that will make it a bit clearer). I have applied a report level filter of "date", which takes into account only the values corresponding between Jan 1st to Apr 30th of 2023. I would like this to be represented in the table as per the second snippet provided.

You got the correct numbers as per my Excel snippet as I have not added my full dataset, and only provided a sample. Apologies if that caused any confusion, I've added a fuller representation of the dataset.