Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX expression for everything else or rest of (with slicer)

Hi Everyone,


Still new to Power BI and getting better at DAX but this one stumps me.  For hours.  And hours.  Please someone smarter than me help!

 

Desired outcome:

Determine the number of orders per month by region for my organization, the entire brand (including me), and everyone in the entire brand (excluding me).  I have solved the first two but cannot create correct DAX for orders for everyone in the entire brand except me.

 

My Data:

I have a table called 'Door_Dash2'. 

I have created a many to one relationship with 'Door_Dash2' [Site Number]  to 'Store Attributes' [Unit Number].

 

My slicer:

I have a slicer using the 'Store Attributes'[Number/Region] field.  This allows me to switch between nine different regions. 

 

Good Measures (Measures that work with slicer):

 

 

Orders Avg My Organization= 
CALCULATE(
	AVERAGE('Door_Dash2'[Orders]),
	'Door_Dash2'[                                Combination Name]
		IN { "MY ORGANIZATION" }
)

that works.  this works too:

 

 

 

Orders Avg ALL INCLUDING MY ORGANIZATION = 
CALCULATE(
    AVERAGE('Door_Dash2'[Orders])
    ,ALL('Door_Dash2'[      Site  Number])
    ,ALL('Store Attributes'[Number/Region]
))

 

Bad measure (Measure that does not work with slicer):

 

but i can't figure out how to write DAX for average orders for all, excluding my organization.

 

 

What I tried:

 

 

Orders Avg ALL EXCLUDING MY ORGANIZATION = 
CALCULATE(
	AVERAGE('Door_Dash2'[Orders]),
   'Door_Dash'[                                Combination Name]
		<> { "MY ORGANIZATION" }
)

This returns the same value as Orders Average My Organization.

 

 

I suspect this is because the slicer of 'Store Attributes'[Number/Region].

 

The issue with my data is that not every 'Door_Dash2'[         Site  Number] has a'Store Attributes'[Number/Region].

 

This is because I do not own all the stores in 'Door_Dash2'.  There are other organizations who own sites.

 

Summary:

In summary, I need a DAX expression that calculates the average orders for all sites, excluding my sites filtered by region using my slicer.

 

Sample data:

 

https://docs.google.com/spreadsheets/d/1ghXDw_l2h_B0MeNffUZtFdiy-AInsMk15RoIqQNIsHU/edit?usp=sharing

 

Thanks so much for looking at this!

1 ACCEPTED SOLUTION

Hi @Anonymous,

Thanks for your understanding and if your problem could be closed, could you please mark the reply as Answered?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

12 REPLIES 12
tachydidaxy
Helper I
Helper I

 

Can you try the following and let us know if it works for your needs?

 

Orders Avg ALL EXCLUDING MY ORGANIZATION =
CALCULATE (
    AVERAGEX (
        FILTER (
            ALL ( 'Door_Dash2' ),
            Door_Dash2[Combination Name] <> "MY ORGANIZATION"
        ),
        'Door_Dash2'[Orders]
    )
)

 

Anonymous
Not applicable

@tachydidaxythis is very close!  the result is average orders of 32.53 which is the correct result for the universe, but I want the average order for whatever region is selected in the slicer. 

 

For example:

if the slicer is [001KC] the average order result would be 36

if the slicer is [002GREEN] the average order result would  be 42

if the slicer is [003SLC] the average order result would be 28.22

 

Make sense?

v-danhe-msft
Employee
Employee

Hi @Anonymous,

Based on my test, you could refer to below measure:

Orders Avg ALL EXCLUDING MY ORGANIZATION = 
CALCULATE(
	AVERAGE('Store Attributes'[Orders]),FILTER('Store Attributes',
   'Store Attributes'[Combination Name]
		<> "MY ORGANIZATION"
))

Result:

1.PNG

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-danhe-msftYes I agree that should work and that is the correct result, but when I insert the expression into Power BI it returns a blank!

 

I think that is because the [Number/Region] slicer is from another table. 

And not all site numbers in 'Store_Attributes' are tied to a [Number/Region].

And only the sites I own are tied to [Number/Region].

 

I feel like an I need an expression that says something like: Look at slicer region selected and calculate the average of all orders that do not have a number/region in the selected slicer region.

 

Make sense?

 

Here's the sample data again.

 

https://docs.google.com/spreadsheets/d/1ghXDw_l2h_B0MeNffUZtFdiy-AInsMk15RoIqQNIsHU/edit#gid=0

Hi @Anonymous,

From your data, I think it should be blank right? When you choose the items in the slicer, the data in [Combination Name] is all 'MY ORGANIZATION', which should be filtered by the measure I post before:

1.PNG

 

Orders Avg ALL EXCLUDING MY ORGANIZATION = 
CALCULATE(
	AVERAGE('Store Attributes'[Orders]),FILTER('Store Attributes',
   'Store Attributes'[Combination Name]
		<> "MY ORGANIZATION"
))

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Dear All,

 

I saw this DAX expression and I think I can use it to create a new average measure by Priority, filtering some of the Status fields out:

 

Orders Avg ALL EXCLUDING SOME=

CALCULATE(

AVERAGE (WHD-Tichets[Work Hours]), FILTER(WHD_Ticktes,

WHD_Tickets[Status]
      <> "Open"

))

 
My question is, I need to exclude more than one option (in this formula shows 'Open',
but need also 'Canceled' and other 2 more), is this possible?
The final result should look similar to the chart on the right (i get the table on the left):
Capture2.JPGCapture1.JPG
 
 
 
Anonymous
Not applicable

@v-danhe-msft   yes i think you are correct.  but i still want to find the average orders for stores not in my organization.  so maybe i am dealing with more of a data issue.

 

so how would i edit my 'Door_Dash2' table to be able to add that measure in PBI?

 

 

PBI file:

https://drive.google.com/open?id=1XU2pUboeC5ogvLMIScfuph5T3nT04Ru_

Hi @Anonymous,

Due to I could not modify your row data and I could not find any way to get your answer if there is no data to average, I think if you want to get correct result, you need to make sure there is data in [Orders] after you have filtered out the data that is not in "MY ORGANIZATION".

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-danhe-msft  thanks so much for taking time out of your busy day to look at this.  I will keep working on a solution as the more I think about it, it is a data issue, not a Power BI issue.  Thanks again.

Hi @Anonymous,

Thanks for your understanding and if your problem could be closed, could you please mark the reply as Answered?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Sharing the PBIX would help a lot since you've got multiple tables and we can't see how they're all related.

 

It could be that your slicer isn't propagating as you'd expect, but it's hard to tell without seeing the data model.

Anonymous
Not applicable

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.