Average Displayed Calculated Values when Pivot Table is Collapsed from Days to Months

[vitalysemenov]

Hello everyone. This is my first attempted at making a post so I hope I will do it correctly.

SCENARIO

In the pivot table shown below, I have Revenue, Units, and a measure calculating Revenue per Unit. This data is broken down by location on rows and by date on columns.

Current formula I'm using for the measure is:

= SUMX ( Table1, [Revenue] ) / SUMX ( Table1, [Units] )

In the picture above, the Revenue per Unit has been calculated correctly (and I want to keep it this way), however when I collapse dates on columns to months (picture below) I want the value displayed to be Average Revenue per Unit for that month instead of how it currently calculates. If I take an average of Revenue per Unit values for all dates in January (from above picture), I would get $106.31 instead of $84.30 as shown below when dates are collapsed.

QUESTION

What formula should I use in my measure that will show Revenue per Unit when pivot table is expanded by date and Average of all these Revenue per Unit when pivot table is collapsed by month?

I hope my question make sense and I thank you in advance for your time and help!

SAMPLE DATA

Date	Units	Location	Revenue
1/1/2021	734	Location 1	$46,994
1/2/2021	253	Location 1	$49,652
1/3/2021	480	Location 1	$13,519
1/4/2021	435	Location 1	$99,552
1/5/2021	204	Location 1	$86,556
1/6/2021	1,239	Location 1	$93,718
1/7/2021	784	Location 1	$87,525
1/8/2021	712	Location 1	$11,324
1/8/2021	435	Location 1	$67,789
1/9/2021	222	Location 1	$35,187
1/9/2021	690	Location 1	$98,764
1/10/2021	489	Location 1	$14,669
1/10/2021	559	Location 1	$51,469
1/11/2021	360	Location 1	$37,264
1/11/2021	661	Location 1	$39,753
1/12/2021	454	Location 1	$17,219
1/12/2021	1,177	Location 1	$22,198
1/13/2021	553	Location 1	$41,481
1/13/2021	845	Location 1	$39,900
1/13/2021	838	Location 1	$26,069
1/13/2021	300	Location 1	$13,980
1/13/2021	719	Location 1	$95,348
1/13/2021	1,038	Location 1	$50,489
1/14/2021	330	Location 1	$87,334
1/14/2021	389	Location 1	$50,580
1/15/2021	790	Location 1	$69,353
1/15/2021	509	Location 1	$89,890
1/16/2021	568	Location 1	$78,931
1/16/2021	1,073	Location 1	$89,170
1/17/2021	560	Location 1	$25,210
1/17/2021	641	Location 1	$51,997
1/18/2021	604	Location 1	$15,832
1/18/2021	1,112	Location 1	$58,454
1/19/2021	725	Location 1	$69,381
1/19/2021	1,061	Location 1	$98,986
1/20/2021	1,245	Location 1	$72,291
1/20/2021	864	Location 1	$20,011
1/20/2021	390	Location 1	$23,985
1/20/2021	535	Location 1	$49,610
1/20/2021	1,224	Location 1	$42,312
1/20/2021	409	Location 1	$76,771
1/21/2021	313	Location 1	$34,576
1/21/2021	227	Location 1	$73,341
1/22/2021	403	Location 1	$90,791
1/22/2021	1,007	Location 1	$51,189
1/23/2021	913	Location 1	$91,369
1/23/2021	920	Location 1	$65,775
1/24/2021	490	Location 1	$61,361
1/24/2021	607	Location 1	$85,550
1/25/2021	782	Location 1	$59,288
1/25/2021	265	Location 1	$6,047
1/26/2021	1,152	Location 1	$90,351
1/26/2021	610	Location 1	$53,851
1/27/2021	861	Location 1	$46,896
1/27/2021	849	Location 1	$75,808
1/28/2021	688	Location 1	$66,569
1/28/2021	444	Location 1	$79,169
1/29/2021	414	Location 1	$20,926
1/30/2021	616	Location 1	$64,323
1/31/2021	1,021	Location 1	$26,946
2/1/2021	1,221	Location 1	$26,683
2/2/2021	1,049	Location 1	$74,268
2/3/2021	514	Location 1	$62,176
2/4/2021	681	Location 1	$32,613

[DataInsights]

@vitalysemenov,

Try these measures:

Sum of Revenue = SUM ( Table1[Revenue] )

Sum of Units = SUM ( Table1[Units] )

Revenue per Unit =
VAR vTable =
    ADDCOLUMNS (
        VALUES ( DimDate[Date] ),
        "@RevPerUnit", DIVIDE ( [Sum of Revenue], [Sum of Units] )
    )
VAR vResult =
    AVERAGEX ( vTable, [@RevPerUnit] )
RETURN
    vResult