How to calculate average of sums (aka level metric/LOD expression)

[smp0150]

I need help creating an average for Product Sub-Category. If I create a basic DAX average function - I get the average for product level. What I need is the average for sum Product-Category. If I calculate in Excel, I get average for Product Sub-Category = 77202.49. Any help apperciated - thanks!

[Eric_Zhang]

---

@smp0150 wrote:

I need help creating an average for Product Sub-Category. If I create a basic DAX average function - I get the average for product level. What I need is the average for sum Product-Category. If I calculate in Excel, I get average for Product Sub-Category = 77202.49. Any help apperciated - thanks!

---

@smp0150

Are you looking for some measure like 

avg = CALCULATE(AVERAGE(yourTable[Value], ALLEXCEPT(yourTable, YourTable[Product-Category]))

Your requirement is not quite clear, could you post any sample data and expected output? When posting any sample, either in a file share link or in plain text, a snapshot is hard to import data.

[smp0150]

Hi @Eric_Zhang, 

Thanks for your response. I tried your suggested formula but had no luck (too many arugments error). Anyway, what's happening is I'm getting an average for profit at a given product sub-category grouping, vs overall average for sum of profit for product sub-categories. 

When I dump into excle - here's how I get the number I'm looking for overall average for all Product Sub-Categories. 

I was able to get the 77202.49 number by creating a summary table. 

Agg Product Sub-Category = SUMMARIZE(orders, Orders[Product Sub-Category], "SumPSC", (SUM(Orders[Profit])))

...and creating a new column...

AvgPSC = AVERAGE('Agg Product Sub-Category'[SumPSC])

However I was hoping there was just a straight calculation I could do vs creating a summary table. MicroStrategy and Tableau have level/dimensionality metrics and level of detail expressions that can be defined at the metric/measure level.  Please let me know. Thanks!

Here's the data:

Product Sub-Category	Profit
Appliances	121651.4
Binders and Binder Accessories	226572.5
Bookcases	-7708.75
Chairs & Chairmats	165348.9
Computer Peripherals	87917.84
Copiers and Fax	129156.7
Envelopes	46133.22
Labels	17775.32
Office Furnishings	92209.23
Office Machines	168072.8
Paper	35361.62
Pens & Art Supplies	1195.903
Rubber Bands	-2841.72
Scissors, Rulers and Trimmers	-1936.85
Storage & Organization	8078.805
Tables	-72495.1
Telephones and Communication	297950.5

[Anonymous]

I didn't read this post, but just fixing the error for Eric 🙂

avg = CALCULATE(AVERAGE(yourTable[Value]), ALLEXCEPT(yourTable, YourTable[Product-Category]))

[uksrik]

Thanks, It worked like a charm!!!

I used the  simple calculation below:

AverageLevelMetric =
CALCULATE(AVERAGE(Table1[Units]), ALLEXCEPT(Table1, Table1[Location]))

[smp0150]

Thanks @Anonymous,

I tried the formula and got the same value as Average Profit: 

[Anonymous · Accepted solution]

Looks like you want the average, over all the Profit Per Sub-Cat?

Total Profit = SUM(Orders[Profit])

Avg Profit - All SubCat =AVERAGEX(ALL(Orders[Product Sub-Category]), [Total Profit])

[smp0150]

Thanks @Anonymous - Got it!! Avg Profit - All Sub Cat = AVERAGEX(ALL(Orders[Product Sub-Category]), [Total Profit])

THANK YOU!!