Average of percentages

[esquiveljc]

Hi,

I have problems with calculating the average of percentages. For example I have this table:

Year	NbX	Color
2019	1	Black
2019	3	Black
2019	3	Red
2019	5	Red
2019	7	Red
2020	3	Green
2020	9	Green
2020	8	Black
2020	3	Red
2020	4	Red
2021	6	Green
2021	7	Green
2021	5	Black
2021	2	Black
2021	4	Red
2022	3	Black
2022	7	Green
2022	8	Red
2022	7	Green
2022	6	Black
2023	7	Red
2023	4	Red
2023	2	Green
2023	2	Green
2023	9	Black

Here are my measures:

SUMNbX = SUM('Table'[NbX])
PercGreen = DIVIDE(CALCULATE([SUMNbX], 'Table'[Color] = "Green"), [SUMNbX])
AverageGreen = AVERAGEX('Table',[PercGreen])

I want the average of the percentages per year so when I put in a table my PercGreen measurement it gives:

And it's correct, what I expect is 44% + 54% + 45% + 17% / 4 = 40% But my AverageGreen measure doesn't work, can you help me please?

Thanks

[FreemanZ · Accepted solution]

hi @esquiveljc ,

try like:

AverageGreen =

AVERAGEX(

    VALUES(table[Year]),

    CALCULATE(

         DIVIDE(

             SUM(table[NBX]),

             CALCULATE(

                 SUM(table[NBX]),

                 table[Color] = "Green"

              )

         )

    )

)

[esquiveljc]

Hi @FreemanZ , 

Sorry, now I need something else please: My AverageGreen is calculated over 4 years, is there a way to force AVERAGEX over 5 years even if the data does not exist?

Thanks!

[FreemanZ]

hi @esquiveljc ,

then try like:

AverageGreen =

    CALCULATE(

         DIVIDE(

             SUM(table[NBX]),

             CALCULATE(

                 SUM(table[NBX]),

                 table[Color] = "Green"

              )

         )

    )/5

[esquiveljc]

Hi @FreemanZ 

No, it doesn't work. It should give me 32.08%.

Any ideas?

Thanks

[esquiveljc · Accepted solution]

Hi @FreemanZ ,

I created a year dimension table with

SUMMARIZE('Table','Table'[Year])

This way I have all the years in a separate table. I then modified the formula as follows:

AverageGreen =

    AVERAGEX(

                       VALUES('Year'[Year])

                      , CALCULATE( [PercGreen] + 0 )

    )

This way we tell AVERAGEX that we have 5 years 😊

[esquiveljc]

Excellent!

But the formula is rather:

AverageGreen =

AVERAGEX(

    VALUES('Table'[Year]),

    CALCULATE(

        DIVIDE(

            CALCULATE(SUM('Table'[NbX]), 'Table'[Color] = "Green"),

            SUM('Table'[NbX])

            )

            )

        )

😊
Thank you @FreemanZ 

[FreemanZ · Accepted solution]

hi @esquiveljc ,

try like:

AverageGreen =

AVERAGEX(

    VALUES(table[Year]),

    CALCULATE(

         DIVIDE(

             SUM(table[NBX]),

             CALCULATE(

                 SUM(table[NBX]),

                 table[Color] = "Green"

              )

         )

    )

)