Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
esquiveljc
Helper I
Helper I

Average of percentages

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:

 

esquiveljc_0-1730486591360.png

esquiveljc_1-1730486866022.png

 

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

 

2 ACCEPTED SOLUTIONS
FreemanZ
Super User
Super User

hi @esquiveljc ,

 

try like:

AverageGreen =

AVERAGEX(

    VALUES(table[Year]),

    CALCULATE(

         DIVIDE(

             SUM(table[NBX]),

             CALCULATE(

                 SUM(table[NBX]),

                 table[Color] = "Green"

              )

         )

    )

)

View solution in original post

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 😊

View solution in original post

6 REPLIES 6
esquiveljc
Helper I
Helper I

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!

hi @esquiveljc ,

 

then try like:

AverageGreen =

    CALCULATE(

 

         DIVIDE(

             SUM(table[NBX]),

             CALCULATE(

                 SUM(table[NBX]),

                 table[Color] = "Green"

              )

         )

    )/5

 

Hi @FreemanZ 

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

 

esquiveljc_0-1730838970034.png

 

Any ideas?

Thanks

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
Helper I
Helper I

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
Super User
Super User

hi @esquiveljc ,

 

try like:

AverageGreen =

AVERAGEX(

    VALUES(table[Year]),

    CALCULATE(

         DIVIDE(

             SUM(table[NBX]),

             CALCULATE(

                 SUM(table[NBX]),

                 table[Color] = "Green"

              )

         )

    )

)

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.