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,
I am very new to Power BI (and definitely to DAX) and I can't seem to figure out where the error is, hope someone out there can help me out!
What I want to achieve, is getting an average per store, weighted by store visits.
STORES | STORE VISITS |
Store 1 | 3 |
Store 2 | 4 |
Store 3 | 6 |
Store 4 | 2 |
STORE 1 | STORE 2 | STORE 3 | STORE 4 | |
PRODUCT A | 7 | 5 | 7 | 4 |
PRODUCT B | 3 | 1 | 6 | 8 |
PRODUCT C | 6 | 4 | 5 | 5 |
So if we would go from this example, I would need to get following table:
STORE 1 | STORE 2 | STORE 3 | STORE 4 | |
PRODUCT A | 2,33 | 1,25 | 1,17 | 2 |
PRODUCT B | 1 | 0.25 | 1 | 4 |
PRODUCT C | 2 | 1 | 0.83 | 2,5 |
I don't get this result just by calculating average, though. It seems that it only divides the number of products by the amount of store visits which was needed to find it.
So for example: If Product A was found 7 times in Store 1; in total there have been 3 store visits in Store 1; but Product A was found 7 times in only 2 store visits => It will divide 7/2.
Other things u might need to know:
The closest I've gotten so far is:
Solved! Go to Solution.
@Anonymous
Can you try this measure please:
Measure =
DIVIDE(
COUNT(DatalijstWinkelbezoek[Product Type]),
CALCULATE(
[#Storevisits],
ALLSELECTED(DatalijstWinkelbezoek[Product Type])
)
)
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
Can you try this measure please:
Measure =
DIVIDE(
COUNT(DatalijstWinkelbezoek[Product Type]),
CALCULATE(
[#Storevisits],
ALLSELECTED(DatalijstWinkelbezoek[Product Type])
)
)
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks a lot!
I feel like I've tried very similar techniques but never got it right, I knew it was going to be something simple..
HI @Anonymous ,
Not very clear.
Can you share a sample .pbix or sample data in text format.
Regards,
Harsh Nathani
Hey,
Okay I tried converting my data in sample data, hopefully, this makes it more clear. The data shown is limited, but I assume it should be enough to make my point.
I added the needed tables for information:
- I want to calculate the average of times Product Type appears per Store, divided by the amount of Store Visits.
=>This way I want to have a sort of weighted average to compare how often a Product Type appears in each store.
- The way it calculates the average now: It doesn't divide the total times the Product Type appears, by the total amount of Store Visits, just by the amount of Store Visits whereby this Product Type was registered.
I hope this makes more sense now...
Thanks in advance, internet wizards!
Your sample tables are inconsistent with your explanation. The first table shown seems to be an aggregate - we would need to see the raw data. The second table hopefully is just a visual as well. If not then you MUST unpivot it before proceeding.
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
53 | |
46 | |
15 | |
12 |