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.
I have a matrix visualization pulling several metrics in rows by date columns. Several of my metrics are calculated columns to get averages for example, total revenue / customer number to get average revenue per customer. But not all my metrics are per customer averages, so I have this measure to have some matrix rows as averages and some as sums (like total revenue) based off an added column listing each as "Sum" or "Avg".
ValueWithAggregation = IF(MIN('Booking Date'[Aggregation])="Avg", AVERAGE('Booking Date'[Value]), IF(MIN('Booking Date'[Aggregation]) = "Sum", SUM('Booking Date'[Value]), 0))
However, when i move up to weeks/months, for example it's taking each date and averaging the daily averages. How can I get it to sum the revenue metric then divide by the sum of customer metrics as the time drilldown changes for an accurate average?
@Anonymous,
You may take a look at the following post.
Could you elaborate? I don't see how that relates...
You probably need to use something like HASONEVALUE. Can't be absolutely sure without sample data to play with and expected output. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Also, see if this Quick Measure, Matrix Measure Total Triple Threat Rock & Roll helps:
So, for example it's showing .90 is the average Passengers per PNR for the month of August (attached picture). But what it's doing is taking the daily Passengers per PNR for each day in August, and averaging them, but they're already averages. When I need it to do a real average as below:
August Passengers per PNR | |||||
8/28/2018 | 0.625 | 8/28/2018 | 5000 | 8000 | |
8/29/2018 | 0.833333 | 8/29/2018 | 7500 | 9000 | |
8/30/2018 | 1 | 8/30/2018 | 10000 | 10000 | |
8/31/2018 | 1.136364 | 8/31/2018 | 12500 | 11000 | |
avg of avgs | 0.898674 | sum | 35000 | 38000 | |
actual average | 0.921053 |
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |