Add New Measure based on Column in Matrix visualization

[JKG]

I have a matrix visualization that I created in PowerBI that looks like this.  What I want to do is add a row (like the Total Row) that shows the average Hours/Pilot for the month subtotaled by the Equipment type.  The number is a summation for each individual, so when I tried to add an average, it wanted to average the underlying data for the summation.  Any ideas?

[v-jiascu-msft · Accepted solution]

Hi @JKG,

I created a similar demo. Please download it from the attachment. 

The problem here is we can't add a row. But we can get the result in the Total row. Please refer to the snapshot below.

totalIsEverage =
IF (
    HASONEVALUE ( DimProduct[ColorName] ),
    SUM ( Sales[SalesQuantity] ),
    AVERAGEX (
        SUMMARIZE (
            Sales,
            'Calendar'[Datekey].[Year],
            'Calendar'[Datekey].[Month],
            DimProduct[BrandName],
            DimProduct[ColorName],
            "totalSales", SUM ( Sales[SalesQuantity] )
        ),
        [totalSales]
    )
)

Best Regards,

[JKG]

Thank you so much!  It worked.  I also discovered it worked to flip the data and add a calculated column using the following formula....

Avg Pilot BH = SUMX(BH_PER_PILOT,BH_PER_PILOT[ACT_BLK_TM]/DISTINCTCOUNT(BH_PER_PILOT[PILOT_EMP_NBR]))

Thanks again!