- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Need Help with Calculating Unweighted Averages at Multiple Hierarchical Levels in Power BI
I am trying to calculate unweighted averages at three different hierarchical levels (A, B, and C) in Power BI using DAX. I have a table structured with values at different levels, and I want to calculate the average at each level using the averages from the lower levels. Here's a breakdown of the levels:
- Level C: The lowest level, where I calculate the average for each category.
- Level B: The next level, where I want to calculate the average of the averages from Level C.
- Level A: The highest level, where I want to calculate the average of the averages from Level B.
For example, for each B-category, I want to calculate the average of the C-category averages. For each A-category, I want to calculate the average of the B-category averages.
I have a sample table with columns like KatA, KatB, KatC, and Value.
Here’s the formula I’ve tried so far for calculating the average at Level B:
DAXKopier kodeAvg_B = AVERAGEX( SUMMARIZE( Farger, Farger[KatB], Farger[KatC], "Avg_C", AVERAGE(Farger[Verdi]) ), [Avg_C] )However, I am having trouble with getting the correct result. For example, when calculating the average for B1 based on C1, C2, and C3, the result should be an unweighted average (e.g., (2.67 + 2.50 + 4.33) / 3 = 3.17), but the formula is giving me a different result.
Could someone help me figure out what I might be missing, or provide suggestions on how to correctly compute these unweighted averages across hierarchical levels?
Thanks in advance for your help!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank 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!!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello @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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank 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!!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Fia123
It sounds good. could you please accept my solution 😊. It will help others to find more easily.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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: FKSS
Kat1 | 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_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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
21 | |
17 | |
16 | |
7 | |
5 |
User | Count |
---|---|
31 | |
27 | |
20 | |
13 | |
12 |