March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all,
I have a set of stores which I use as a mapping key. These set of stores will be filtered into either Tier 1 or Tier 2 stores. I have the revenue of each store by month and the size of each store in the table as well.
Is there anyway I can calculate this using a calculated column so that when I add in a filter and group sums tgt, the aggregated revenue/sqm is the correct amount?
Basically I just want to calculate an revenue/sqm which I can put in my pivot table. Thanks
Please bear in mind that my data is by month, but I'm analysing by year so if I sum up the size column, it gives me a value too big, but if i use average, it gives me a value too small.
My head is about to explode trying to figure this out so I'd appreicate any help whatsoever.
Solved! Go to Solution.
Oh! You have a Groups/Super Groups problem on your hands. So in your size measure, you are going to need to use SUMMARIZE to get the AVERAGE of each individual store (which will give you the actual store size) in the group and then just use SUM to SUM the AVERAGE of the sizes. See this article:
https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
Sorry, none of that made any sense until you mentioned your related table for grouping.
Awesome! Glad we got there!!
Hi,
Your question is not clear. Based on the data you share, show the expected result.
Oh! You have a Groups/Super Groups problem on your hands. So in your size measure, you are going to need to use SUMMARIZE to get the AVERAGE of each individual store (which will give you the actual store size) in the group and then just use SUM to SUM the AVERAGE of the sizes. See this article:
https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
Sorry, none of that made any sense until you mentioned your related table for grouping.
Does the square meter of you store change every month? Because if you had data like:
Store,Revenue,SQM,Month,Year
Store1,$1000,10,1,2018
Store1,$2000,10,2,2018
If the square meters of the store is the same then if you take the average of it, you should get the square meters of the store regardless of how you aggregate. Or just use MAX or MIN? Need to see data.
Can you show us what your data/tables looks like?
Or provide a sample PBIX?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
85 | |
78 | |
65 | |
59 |
User | Count |
---|---|
140 | |
122 | |
105 | |
94 | |
90 |