Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
City | Promoted Product | Revenue | Total City Revenue |
London | Rubber Ducky | 100 | 2500 |
London | Christmas Tree | 500 | 2500 |
London | Porridge Mix | 200 | 2500 |
Paris | Rubber Ducky | 600 | 8000 |
Paris | Christmas Tree | 300 | 8000 |
Paris | Porridge Mix | 400 | 8000 |
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
Solved! Go to Solution.
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]
Proud to be a Super User!
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]
Proud to be a Super User!
Works perfectly - thank you!
@Anonymous , Try if this measure can help
AverageX(summarize( Table, Table[City],"_1" ,Sum(Table[Revenue]), "_2", max(Table[City Revenue])),divide([_1],[_2]))
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |