Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
hi @esquiveljc ,
try like:
AverageGreen =
AVERAGEX(
VALUES(table[Year]),
CALCULATE(
DIVIDE(
SUM(table[NBX]),
CALCULATE(
SUM(table[NBX]),
table[Color] = "Green"
)
)
)
)
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 😊
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 ,
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 😊
Excellent!
But the formula is rather:
😊
Thank you @FreemanZ
hi @esquiveljc ,
try like:
AverageGreen =
AVERAGEX(
VALUES(table[Year]),
CALCULATE(
DIVIDE(
SUM(table[NBX]),
CALCULATE(
SUM(table[NBX]),
table[Color] = "Green"
)
)
)
)
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
20 | |
7 | |
6 | |
5 | |
5 |
User | Count |
---|---|
24 | |
10 | |
10 | |
9 | |
6 |