Forum Discussion

Martynas_BI's avatar
Martynas_BI
Frequent Visitor
3 years ago

multi level filtering

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,

Need Help

2 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    3 years ago

    Hi Martynas_BI 
    Allexcept is the function that worked perfectly:

    sum per region = calculate (sum('Table'[Inventory]),ALLEXCEPT('Table','Table'[Region]))
    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
    • Martynas_BI's avatar
      Martynas_BI
      Frequent Visitor
      3 years ago

      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.