ALL results diverging in Excel compared to PowerBI

[Anonymous]

Hi All,

The All function is giving me divergent results from PowerBI to Excel. I want to calculate the annual budget regardless of the month selected on the slicer. To create this I have built the measure;

Measure ignoring month =calculate([Budget],ALL(CalendarMonth[Fiscal Month Name]))

Here are the results from PowerBI;

And here are the results from Excel;

It's working as expected in Excel, but what could be going on to produce the divergent results???? 

[Anonymous]

You are probably sorting Fiscal Month Name by another column in the underlying model table... This is why ALL is not enough when applied to the column. This is a known issue and if you want to know more about it, you should head to www.sqlbi.com and look for an article about problems with sorting.

[Greg_Deckler]

@Anonymous - Really hard to troubleshoot without sample data. Are you certain that you do not have something like a page level filter in your Power BI report? Page level filters and report level filters prefilter the data before it gets to DAX so even an ALL won't bring the data back once it is filtered by page and report level filters.

[Anonymous]

Understandable - re no data.

I think my observation is a quirk. @MattAllington  details what is happeing here;

https://exceleratorbi.com.au/the-all-function-in-dax/

Cheers

Mark 

[amitchandak]

@Anonymous , one of the two should work as measure

Measure ignoring month =calculate([Budget],ALL(CalendarMonth))
Measure ignoring month =calculate([Budget],removefilters(CalendarMonth[Fiscal Month Name]))

https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

[Anonymous]

Thanks @amitchandak , I tried allexcept fiscal year - whihc seems to hahve fixed my problem I still cant explain why there were different results though. 

[amitchandak]

@Anonymous , In that case, it should keep filter of year and remove everything else.