barcelona

more panorama pictures

excel

1 click excel macro tool

Power BI: calculate average based on 2 columns with unique number

Sometimes, I need to calculate the average of something based on another column in which there are duplicate numbers. So my goal is to calculate the average but only for unique number.

Let’s imagine that I have this data:

power bi

As we can see, in the “number” column, I have some duplicates and what I want, it is to calculate the average percentage by excluding them. For that, I will use this formula:

AVERAGEX(VALUES('table'[argument]),CALCULATE(AVERAGE('table'[argument])))

NOTE: change “table” and “argument” by yours. As you can see in the picture, I put mine.

If it is correct, the result should be 44.39% (36.09% if I take in account the duplicate) according to excel.

Let’s check that, I will create a measure and put the formula:

power bi

I will format this measure in “percentage”:

power bi

Now I will create a card and put this measure:

power bi

And the result matches with excel.

power bi

Interesting Management