Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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 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!
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.
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 |
I didn't read this post, but just fixing the error for Eric 🙂
avg = CALCULATE(AVERAGE(yourTable[Value]), ALLEXCEPT(yourTable, YourTable[Product-Category]))
Thanks, It worked like a charm!!!
I used the simple calculation below:
AverageLevelMetric =
CALCULATE(AVERAGE(Table1[Units]), ALLEXCEPT(Table1, Table1[Location]))
Thanks @Anonymous,
I tried the formula and got the same value as Average Profit:
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])
Thanks @Anonymous - Got it!! Avg Profit - All Sub Cat = AVERAGEX(ALL(Orders[Product Sub-Category]), [Total Profit])
THANK YOU!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |