Need Help with Calculating Unweighted Averages at Multiple Hierarchical Levels in Power BI

[Fia123]

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:

1. Level C: The lowest level, where I calculate the average for each category.
2. Level B: The next level, where I want to calculate the average of the averages from Level C.
3. 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:

   DAX

   Kopier kode

   Avg_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!

[lbendlin · Accepted solution]

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...

[v-karpurapud]

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.

 

[Fia123 · Accepted solution]

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!!!

[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: 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_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

[lbendlin · Accepted solution]

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...