Forum Discussion

vitalysemenov's avatar
vitalysemenov
New Member
5 years ago

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

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

DateUnitsLocationRevenue
1/1/2021734Location 1$46,994
1/2/2021253Location 1$49,652
1/3/2021480Location 1$13,519
1/4/2021435Location 1$99,552
1/5/2021204Location 1$86,556
1/6/20211,239Location 1$93,718
1/7/2021784Location 1$87,525
1/8/2021712Location 1$11,324
1/8/2021435Location 1$67,789
1/9/2021222Location 1$35,187
1/9/2021690Location 1$98,764
1/10/2021489Location 1$14,669
1/10/2021559Location 1$51,469
1/11/2021360Location 1$37,264
1/11/2021661Location 1$39,753
1/12/2021454Location 1$17,219
1/12/20211,177Location 1$22,198
1/13/2021553Location 1$41,481
1/13/2021845Location 1$39,900
1/13/2021838Location 1$26,069
1/13/2021300Location 1$13,980
1/13/2021719Location 1$95,348
1/13/20211,038Location 1$50,489
1/14/2021330Location 1$87,334
1/14/2021389Location 1$50,580
1/15/2021790Location 1$69,353
1/15/2021509Location 1$89,890
1/16/2021568Location 1$78,931
1/16/20211,073Location 1$89,170
1/17/2021560Location 1$25,210
1/17/2021641Location 1$51,997
1/18/2021604Location 1$15,832
1/18/20211,112Location 1$58,454
1/19/2021725Location 1$69,381
1/19/20211,061Location 1$98,986
1/20/20211,245Location 1$72,291
1/20/2021864Location 1$20,011
1/20/2021390Location 1$23,985
1/20/2021535Location 1$49,610
1/20/20211,224Location 1$42,312
1/20/2021409Location 1$76,771
1/21/2021313Location 1$34,576
1/21/2021227Location 1$73,341
1/22/2021403Location 1$90,791
1/22/20211,007Location 1$51,189
1/23/2021913Location 1$91,369
1/23/2021920Location 1$65,775
1/24/2021490Location 1$61,361
1/24/2021607Location 1$85,550
1/25/2021782Location 1$59,288
1/25/2021265Location 1$6,047
1/26/20211,152Location 1$90,351
1/26/2021610Location 1$53,851
1/27/2021861Location 1$46,896
1/27/2021849Location 1$75,808
1/28/2021688Location 1$66,569
1/28/2021444Location 1$79,169
1/29/2021414Location 1$20,926
1/30/2021616Location 1$64,323
1/31/20211,021Location 1$26,946
2/1/20211,221Location 1$26,683
2/2/20211,049Location 1$74,268
2/3/2021514Location 1$62,176
2/4/2021681Location 1$32,613

1 Reply

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    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