Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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] ) )
@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?
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:
Regards,
Daniel He
@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:
Orders Avg ALL EXCLUDING MY ORGANIZATION = CALCULATE( AVERAGE('Store Attributes'[Orders]),FILTER('Store Attributes', 'Store Attributes'[Combination Name] <> "MY ORGANIZATION" ))
Regards,
Daniel He
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"
))
@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
@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
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.
@AlexisOlsonyou are right. Here you go.
https://drive.google.com/file/d/1XU2pUboeC5ogvLMIScfuph5T3nT04Ru_/view?usp=sharing
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |