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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Please help. Revenue per sqm

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. 

 

 

 

1 ACCEPTED 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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Thanks smoupre, works like a charm. Dont know why I can't accept your response as a solution but I'll mark it once I get home with my home computer!

Awesome! Glad we got there!!


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Data is like:

A jan 1000 500
A feb 1000 700
B jan 2000 600
B feb 2000 800
C jan 2500 1000
C feb 2500 900

Size never changes across months, but revenue will.
I have relationships on the store which will group them in my pivot, so for example A and B will be tier 1 location, so I need the average to kind of "average" the unique store sizes then Sum the sizes of each unique store.

Thanks!!

Hi,

 

Your question is not clear.  Based on the data you share, show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
dramus
Continued Contributor
Continued Contributor

Can you show us what your data/tables looks like?

 

Or provide a sample PBIX?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.