Average of Subtotals

[bheinrich]

Hello,

I need to generate an average of the subtotals in the below grouping per quarter.  For Q1, It is showing $469,814, but should be $100,431 (the sume of all the grouped names divided by 5).

Here is my current measure:

Cost per Day Average = AVERAGEX(VALUES('Frac Detail'[Zipper_Frac_Pad]),AVERAGEX(VALUES('Frac Detail'[Well]),DIVIDE([Daily Costs],'Frac Detail'[Frac Days])))

 

Any tips? Thank you.

[v-yulgu-msft]

Hi @bheinrich,

You may try to create a measure similar to:

Cost per Day Average =
CALCULATE (
    SUM ( [Costs] ),
    ALLEXCEPT ( table, table[YearNo], table[QuarterNo] )
)
    / CALCULATE (
        DISTINCTCOUNT ( [grouped names] ),
        ALLEXCEPT ( table, table[YearNo], table[QuarterNo] )
    )

For more advice, please share sample data stored in original data tables.

Regards,

Yulana Gu

[bheinrich]

Here is the chart using Cost per Day Average.. it's basically just averaging each name & not the grouping cost per day.  I just need to find a way to average the groupings.

[Greg_Deckler]

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149