cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
twb311 Frequent Visitor
Frequent Visitor

Calculate ALL regardless of slicer

Hi Everyone - Very new to PowerBI so pardon my ignorance - I am sure there is an easy solve for this!

 

I am trying to create a new column/measure that will calculate all orders regardless of my slicer.

 

For example, when the slicer is selected for Washington DC I have three values in the graph.  I want the third value (pink line) to show ALL orders, not just Washington DC orders.

 

Capture.PNG

 

I have tried the following to create a new column/measure:

 

AllOrdersAvg = CALCULATE(AVERAGE(AllMonths[Orders]);ALLSELECTED(AllMonths))

 

But receive the following error:

 

The syntax for ';' is incorrect. (DAX(CALCULATE(AVERAGE(AllMonths[Orders]);ALLSELECTED(AllMonths))))

 

Also tried:

 

AllOrdersAvg = ALL(AllMonths[Orders])

 

But received the following error:

 

A table of multiple values was supplied where a single value was expected.

 

What am I doing wrong???

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculate ALL regardless of slicer

Hey,

 

to remove the existing filter that comes from the slicer try this measure:

 

Measure 4 = 
CALCULATE(
    AVERAGE('AllMonths'[Orders])
    ,ALL('AllMonths'[name of the column that contains "Washington DC"])
)

Please be aware that the character that separates arguments inside function depends on your language settings, if you have an english os the above formula should work, but maybe you have to use ;ALL use a semicolon instead of a comma.

 

Regards,

Tom

 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Calculate ALL regardless of slicer

Hey,

 

to remove the existing filter that comes from the slicer try this measure:

 

Measure 4 = 
CALCULATE(
    AVERAGE('AllMonths'[Orders])
    ,ALL('AllMonths'[name of the column that contains "Washington DC"])
)

Please be aware that the character that separates arguments inside function depends on your language settings, if you have an english os the above formula should work, but maybe you have to use ;ALL use a semicolon instead of a comma.

 

Regards,

Tom

 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

Highlighted
Super User
Super User

Re: Calculate ALL regardless of slicer

One of the most important things in DAX is the concept of filters.  When you put data into a chart/table/ect, there are filters.  That is why you get a different answers.  With ALL you want to tell DAX to ignore the current filter and use an entire table or an entire column.  A good practice is to never filter an entire table if you can filter a column.  

 

So using the Contoso DB as an example:

1) Create a base measure (which looks like is a count or sum of orders)

 

Total Sales = SUM ( Sales[Unit Price] )

2) Create a measure, using the base measure, that will give you the total regardless of what is selected in a slicer, or what other filter context exist:

 

 

All Sales = 
CALCULATE(
	[Total Sales],
	ALL ('Product'[Brand],'Product'[Color])
)

So in this example, ALL ignores the filters from the Product Table of Brand and Color. Any other columns will not be ignored, and that filter context will be used to evaluate Total Sales. There is also ALL's cousin, ALLEXCEPT. Output with Product and Color on rows:

 

Product Color ALL example.png

 

ALLSELECTED is an interesting function.  It works like you would expect, most of the time.  It actually removes the current filter context and restores the previous one.  That's a gross oversimplification, but something to keep in mind and maybe explore further.  

 

All Sales, of only Selected =
CALCULATE(
	[Total Sales]
	ALLSELECTED ('Product'[Brand],'Product'[Color])  /*Can Also use ALLSELECTED(), which uses the whole data model*/
)

All Selected Example.png

 

Hope that helps

 

twb311 Frequent Visitor
Frequent Visitor

Re: Calculate ALL regardless of slicer

@TomMartensMany thanks this worked amazingly well and easy; I would have never been able to figure it out myself.  Thank you!

 

And @Nick_M this looks like a good solution too - thanks!  I will definitely keep it in mind for future reference!

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 284 members 2,955 guests
Please welcome our newest community members: