Forum Discussion
Helper IINeed Help with Calculating Unweighted Averages at Multiple Hierarchical Levels in Power BI
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Thank you so much for taking the time to look into my problem. The issue is now resolved – and I don’t know what happened. I was playing around with the numbers without changing the measures – and suddenly the correct number appeared. Strange!!!
Community SupportHello Fia123
Hope you are doing well!
Thanks for reaching out to the Microsoft Fabric Community Forum.
I have reproduced your scenario with a sample dataset. It worked fine for me . I hope this will work for you as well . You can try the following steps to resolve the issue.
1.After importing the dataset into power bi create an individual measures for averages.
Level C Average:
LevelC_Avg = AVERAGEX(
SUMMARIZE(Farger, Farger[KatC], "Avg_C", AVERAGE(Farger[Value])),
[Avg_C]
)
Level B Average:
LevelB_Avg = AVERAGEX(
SUMMARIZE(
Farger,
Farger[KatB],
"Avg_C", AVERAGEX(
SUMMARIZE(Farger, Farger[KatC], "Avg_C", AVERAGE(Farger[Value])),
[Avg_C]
)
),
[Avg_C]
)
Level A Average:
LevelA_Avg = AVERAGEX(
SUMMARIZE(
Farger,
Farger[KatA],
"Avg_B", AVERAGEX(
SUMMARIZE(
Farger,
Farger[KatB],
"Avg_C", AVERAGEX(
SUMMARIZE(Farger, Farger[KatC], "Avg_C", AVERAGE(Farger[Value])),
[Avg_C]
)
),
[Avg_C]
)
),
[Avg_B]
)
2. Now Add matrix visual to report pane and drag fields and measures into the appropriate sections of the Matrix:
- Rows: Add KatA, KatB, and KatC to create the hierarchy.
- Values: Value to show raw data values, Avg_C to show Level C averages, Avg_B to show Level B averages, Avg_A to show Level A averages.
3.You should see a hierarchical display of the data with averages calculated at each level.
Level A
Level B Level C
I hope you will get the solution as per the requirements you mentioned above.If you’re still experiencing issues, feel free to reach out to us for further assistance!
If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.
Helper IIThank you so much for taking the time to look into my problem. The issue is now resolved – and I don’t know what happened. I was playing around with the numbers without changing the measures – and suddenly the correct number appeared. Strange!!!
- v-karpurapud
Hi Fia123
It sounds good. could you please accept my solution 😊. It will help others to find more easily.- Fia123
Hi,
As I mentioned in my previous email, the issue suddenly resolved itself, and the numbers were correct. Now, I’m working in a different report, and the same problem has appeared again.
When I calculate the average at the top level (AVG_Kat1), I get an incorrect value. The correct value is 5.89 (5.77 + 6.0)/2), but I am getting 5.86.
Power BI gives:Kat1 Avg_Kat1
--------------------
Pre-K 5,77
Toddler 6,01
--------------------
Total 5,86
I got the following data:
Tabel: FKSSKat1 Kat2 Kat3 Value Pre-K CO BM 6,75 Pre-K CO ILF 6,75 Pre-K CO PD 6,75 Pre-K ES NC 7 Pre-K ES PC 7 Pre-K ES RSP 6,63 Pre-K ES TS 6,75 Pre-K IS CD 3,5 Pre-K IS LM 3,63 Pre-K IS QF 4 Toddler EBS BG 6,88 Toddler EBS NC 7 Toddler EBS PC 7 Toddler EBS RCP 7 Toddler EBS TS 6,75 Toddler ESL FLD 5,25 Toddler ESL LM 6,13 Toddler ESL QF 3,88 AVG_Kat3 = average(FKSS[Value])Avg_Kat2 = Averagex(
SUMMARIZE(FKSS, FKSS[Kat2], FKSS[Kat3],
"Avg_Kat3",[AVG_Kat3]),
[Avg_Kat3])
Avg_Kat1 =
AVERAGEX(
SUMMARIZE(FKSS,FKSS[Kat1],FKSS[Kat2],
"Avg_Kat2",
AVERAGEX(
SUMMARIZE(
FKSS,
FKSS[Kat2],
FKSS[Kat3],
"Avg_Kat3", AVERAGE(FKSS[Value])
),
[Avg_Kat3])),
[Avg_Kat2])I would be very grateful if someone could help me understand the incorrect result.
Tove