Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Creating a measure that ignores a specific filter context

I'm trying to create measure that would ignore a certain filter context but retain all others. In this case, the one I'm trying to ignore is Year.   I have a fact table for my sales, which has a re...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Greg_Deckler Thanks, this is kind of driving me nuts here. No matter what I try, I am still returning only the sales of the latest year, or 2022. For e.g. I tried:

 

Measure = CALCULATE (
                [Sales],
                ALLSELECTED('Date'[Year])
)
 
And I still end up with the same output as my screenshot above. Even though I know it should return the sum of [Sales] across all years in the matrix. 
 
Any idea what may be causing this behavior? 
 
 
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ,

Can you show me the fields in the visual? Is your year field come from the date hierarchy? If that's the case, please try:

Measure = CALCULATE([Sales],ALL('Date'[Date].[Year]))

But it is recommended to use the Year function in the Date table to create a calculated column and use it in the visual.

 

Best Regards,
Gao
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Anonymous Thanks for getting back! Sorry I am not allowed to show all the fields but take a look at the screenshot below. The year doesn't belong to a heirarchy. Instead, I created a calculated table of all the possible dates between the max and min date in the data model. And then from those list of dates, I created some calculated columns for the month and year. 

     

    One interesting thing I just noticed is that the matrix total is just showing the 2022 sales. Does this offer a hint as to what my problem may be? 

     

     

    Also the sales figure is YTD. So the measure is essentially CALCULATE ( [Sales] , DATESYTD ('Date'[Date]))