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

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

Accepted Solutions
Community Support Team
Community Support Team

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

Hi @twb311,

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.
11 REPLIES 11
Community Support Team
Community Support Team

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

Hi @twb311,

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.
twb311 Frequent Visitor
Frequent Visitor

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

@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

AlexisOlson Member
Member

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

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.

twb311 Frequent Visitor
Frequent Visitor

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

tachydidaxy Regular Visitor
Regular Visitor

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

 

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]
    )
)

 

twb311 Frequent Visitor
Frequent Visitor

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

@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?

Community Support Team
Community Support Team

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

Hi @twb311,

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.
Highlighted
twb311 Frequent Visitor
Frequent Visitor

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

@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_

Community Support Team
Community Support Team

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

Hi @twb311,

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.