topic Re: Average of percentages in DAX Commands and Tips

Average of percentages

esquiveljc — Fri, 01 Nov 2024 18:48:47 GMT

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

Re: Average of percentages

FreemanZ — Fri, 01 Nov 2024 19:33:34 GMT

hi esquiveljc ,

try like:

AverageGreen =

AVERAGEX(

VALUES(table[Year]),

CALCULATE(

DIVIDE(

SUM(table[NBX]),

CALCULATE(

SUM(table[NBX]),

table[Color] = "Green"

)

Re: Average of percentages

esquiveljc — Fri, 01 Nov 2024 19:46:42 GMT

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

Re: Average of percentages

esquiveljc — Tue, 05 Nov 2024 19:28:15 GMT

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!

Re: Average of percentages

FreemanZ — Tue, 05 Nov 2024 19:47:17 GMT

hi esquiveljc ,

then try like:

AverageGreen =

CALCULATE(

DIVIDE(

SUM(table[NBX]),

CALCULATE(

SUM(table[NBX]),

table[Color] = "Green"

)

)/5

Re: Average of percentages

esquiveljc — Tue, 05 Nov 2024 20:37:40 GMT

Hi FreemanZ

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

Any ideas?

Thanks

Re: Average of percentages

esquiveljc — Wed, 06 Nov 2024 14:26:52 GMT

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 😊