Matrix Average Aggregation Only Returning Average of Averages

[Anonymous]

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? 

[v-chuncz-msft]

@Anonymous,

You may take a look at the following post.

https://community.powerbi.com/t5/Desktop/Create-a-Chart-with-Hierarchy-of-Month-and-Saturdays-in-that/m-p/458533#M212588

[Anonymous]

Could you elaborate? I don't see how that relates...

[Greg_Deckler]

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:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

[Anonymous]

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