cancel
Showing results for
Search instead for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

## How to get sum in Marix table subtotal for AVERAGEX?

Hi Team,

If there is any way to get subtotal result for my below example?

I do ROI impact of our promotions, but some of them can run multiple weeks and I need to find difference vs non promo/base weeks sales. This how I do Base Units sales Base Units = CALCULATE(AVERAGEX('1 Total Summary','1 Total Summary'[NON PROMO AVERAGE]),ALLSELECTED('1 Total Summary'))

However, I would like to see sum instead of average in my subtotal result for base units/Matrix Table.

1 ACCEPTED SOLUTION
Helper I

Hi,

I think I nailed it

Base Units =
VAR AvgSales = CALCULATE(AVERAGEX('1 Total Summary','1 Total Summary'[NON PROMO AVERAGE]),ALLSELECTED('1 Total Summary'))

RETURN
IF(HASONEVALUE('Calendar'[Week M]),
AvgSales,
SUMX( VALUES('Calendar'[Week M]),AvgSales))

credits to Enterprise DNA
1 REPLY 1
Helper I

Hi,

I think I nailed it

Base Units =
VAR AvgSales = CALCULATE(AVERAGEX('1 Total Summary','1 Total Summary'[NON PROMO AVERAGE]),ALLSELECTED('1 Total Summary'))

RETURN
IF(HASONEVALUE('Calendar'[Week M]),
AvgSales,
SUMX( VALUES('Calendar'[Week M]),AvgSales))

credits to Enterprise DNA

## Helpful resources

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors