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.
Hi All,
Consider the below data,
Month | Parent | Child | Value |
01 | AB | A | 10 |
02 | AB | A | 10 |
03 | AB | A | 20 |
04 | AB | A | 20 |
05 | AB | A | 30 |
01 | AB | B | 30 |
02 | AB | B | 40 |
03 | AB | B | 40 |
04 | AB | B | 50 |
05 | AB | B | 50 |
01 | DE | D | 15 |
02 | DE | D | 15 |
03 | DE | D | 16 |
04 | DE | D | 16 |
05 | DE | D | 17 |
01 | DE | E | 17 |
02 | DE | E | 18 |
03 | DE | E | 18 |
04 | DE | E | 19 |
05 | DE | E | 19 |
An excel pivot table is shown below with Sum of Values per month.
I would like to calculate the Average of Total sum (as shown below) in PowerBI which would show the average distribution of Value of the Parent.
Summarizing at a parent level won't work since I would like to drill down to child level if required.
Thank you!
Solved! Go to Solution.
Try this measure expression, replace Table with your actual table name throughout. This should get the correct individual and subtotal values.
Avg Sum =
AVERAGEX (
SUMMARIZE ( Table, Table[Parent], Table[Month] ),
CALCULATE ( SUM ( Table[Value] ) )
)
Pat
Thank you for your response! I will try this and get back to you.
Try this measure expression, replace Table with your actual table name throughout. This should get the correct individual and subtotal values.
Avg Sum =
AVERAGEX (
SUMMARIZE ( Table, Table[Parent], Table[Month] ),
CALCULATE ( SUM ( Table[Value] ) )
)
Pat
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |