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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Create a Measure: Average Value Within a Dimension, then Sum the Results

I have two tables. One table has revenue by city and promoted products, and other has revenue just by city. Because the first table does not have all products (only promoted ones), the revenue of all of it's rows added together for a city is less than the revenue of the city overall shown in the second table.

I've merged these tables together so it looks something like:

CityPromoted ProductRevenueTotal City Revenue
LondonRubber Ducky1002500
LondonChristmas Tree5002500
LondonPorridge Mix2002500
ParisRubber Ducky6008000
ParisChristmas Tree3008000
ParisPorridge Mix4008000

 

What I'm trying to do is have a measure (or over time graph as I also have a date column) that shows the percentage of revenue made up by promoted products, and allows this to be filtered by city and/or product. So for instance, if we had no filters it would show 20% ( (100+500+200+600+300+400)/(2500+8000). But if I filtered to say only Christmas Trees it would show 7.6% ( (500+300)/(2500+8000) ) and if I filtered to only Paris it would show 16% (1300/8000).

I'm unsure how to do this because it requires the measure of total_city_revenue ie. the denominator of the percentage calculation to average across 'promoted products' but to sum across 'city'. Maybe I should not have merged the tables this way? Any help much appreciated

1 ACCEPTED SOLUTION
FarhanAhmed
Community Champion
Community Champion

Try to Create 3 measures and see if you are getting desired results.

 

_MAX = MAX('Table'[Total City Revenue])
Total Rev = SUMX(VALUES('Table'[City]),[_MAX])

 

Avg = SUM('Table'[Revenue])/[Total Rev]

 

 







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
FarhanAhmed
Community Champion
Community Champion

Try to Create 3 measures and see if you are getting desired results.

 

_MAX = MAX('Table'[Total City Revenue])
Total Rev = SUMX(VALUES('Table'[City]),[_MAX])

 

Avg = SUM('Table'[Revenue])/[Total Rev]

 

 







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




Anonymous
Not applicable

Works perfectly - thank you!

amitchandak
Super User
Super User

@Anonymous , Try if this measure can help

AverageX(summarize( Table, Table[City],"_1" ,Sum(Table[Revenue]), "_2", max(Table[City Revenue])),divide([_1],[_2]))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.