cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## Averageifs and Countifs in Power Bi

Help, I have 2 formulas in excel AVERAGEIFS and COUNTIFS to get the average of the numbers between 20000 and 21000 and count the numbers that has that values, I already got the table filtered in power bi with same values but cant create the formula to get the information from excel,

2 ACCEPTED SOLUTIONS
Super User

You can create measures like

``````Num rows = COUNTROWS('Table')

Avg = AVERAGE( 'Table'[Cost])``````

and those will work with any filters you have applied. If you want to explicitly bake the filters in to the measures, you could do

``````Num rows between 20000 & 21000 =
CALCULATE (
SUM ( 'Table'[Cost] ),
'Table'[Cost] >= 20000
&& 'Table'[Cost] <= 21000
)

Average between 20000 & 21000 =
CALCULATE (
AVERAGE ( 'Table'[Cost] ),
'Table'[Cost] >= 20000
&& 'Table'[Cost] <= 21000
)``````
Super User

Create a measure like

``Avg Measure = AVERAGE ( 'BV37CC'[CONTRIBUTION COST])``

and use that in conjunction with the calculation group.

Be careful with the measure name, as I think both AVG and AVERAGE are reserved words.

11 REPLIES 11
Frequent Visitor

How can I create the calculation group, I tried to put all the measures in the same calculation group but the calculation group is showing error:

`Avg Calc=  CALCULATE ( AVERAGE ( 'BV37CC'[CONTRIBUTION COST]), 'BV37CC'[CONTRIBUTION COST] >= 20000 && 'BV37CC'[CONTRIBUTION COST] <= 21000 ) AVERAGE ( 'BV37CC'[CONTRIBUTION COST]), 'BV37CC'[CONTRIBUTION COST] >= 21000 && 'BV37CC'[CONTRIBUTION COST] <= 22000 ) AVERAGE ( 'BV37CC'[CONTRIBUTION COST]), 'BV37CC'[CONTRIBUTION COST] >= 22000 && 'BV37CC'[CONTRIBUTION COST] <= 23000 ) AVERAGE ( 'BV37CC'[CONTRIBUTION COST]), 'BV37CC'[CONTRIBUTION COST] >= 23000 && 'BV37CC'[CONTRIBUTION COST] <= 24000 ) AVERAGE ( 'BV37CC'[CONTRIBUTION COST]), 'BV37CC'[CONTRIBUTION COST] >= 24000 && 'BV37CC'[CONTRIBUTION COST] <= 25000 ) AVERAGE ( 'BV37CC'[CONTRIBUTION COST]), 'BV37CC'[CONTRIBUTION COST] >= 25000 && 'BV37CC'[CONTRIBUTION COST] <= 26000 ) AVERAGE ( 'BV37CC'[CONTRIBUTION COST]), 'BV37CC'[CONTRIBUTION COST] >= 26000 && 'BV37CC'[CONTRIBUTION COST] <= 27000 )`

Super User

Each one would need to be its own calculation item

Frequent Visitor

I am trying to create calculation items but I cant create them, it only send -1 value, I think I am missing the SELECTEDMEASURE () thing:

Can you show me an example with the following formula:

CALCULATE (
AVERAGE ( 'BV37CC'[CONTRIBUTION COST]),
'BV37CC'[CONTRIBUTION COST] >= 20000
&& 'BV37CC'[CONTRIBUTION COST] <= 21000)
Super User

You could create a calculation item like

``````CALCULATE (
SELECTEDMEASURE (),
'BV37CC'[CONTRIBUTION COST] >= 20000
&& 'BV37CC'[CONTRIBUTION COST] <= 21000
)
``````

and then repeat that for each group of values. This would work for both the average and the count

Frequent Visitor

I already created calculation item but when I am creating the Matrix Im not sure why the formula is not giving me the correct values as the separeted calculations, I know that the formula of the calculation item goes into the Rows field but what goes into the values field?

Super User

The base measure goes into the values, so whatever works out your count or average

Frequent Visitor

Looks that I am missing something the formulas instead of get the AVG of the selected range are just SUM the total of the selected range as following:

Do you know how can I get the AVG of those?

I am using the formula:

`CALCULATE ( SELECTEDMEASURE (), 'BV37CC'[CONTRIBUTION COST] >= 20000 && 'BV37CC'[CONTRIBUTION COST] <= 21000 )`

Instead of the single measure from PBI

Avg 20-21k =
CALCULATE (
AVERAGE ( 'BV37CC'[CONTRIBUTION COST]),
'BV37CC'[CONTRIBUTION COST] >= 20000
&& 'BV37CC'[CONTRIBUTION COST] <= 21000
)
Super User

Create a measure like

``Avg Measure = AVERAGE ( 'BV37CC'[CONTRIBUTION COST])``

and use that in conjunction with the calculation group.

Be careful with the measure name, as I think both AVG and AVERAGE are reserved words.

Super User

You can create measures like

``````Num rows = COUNTROWS('Table')

Avg = AVERAGE( 'Table'[Cost])``````

and those will work with any filters you have applied. If you want to explicitly bake the filters in to the measures, you could do

``````Num rows between 20000 & 21000 =
CALCULATE (
SUM ( 'Table'[Cost] ),
'Table'[Cost] >= 20000
&& 'Table'[Cost] <= 21000
)

Average between 20000 & 21000 =
CALCULATE (
AVERAGE ( 'Table'[Cost] ),
'Table'[Cost] >= 20000
&& 'Table'[Cost] <= 21000
)``````
Frequent Visitor

what if I need it to be a single table for all calculations example:

I need it like excel:

 VOL COST 4 20,976.97 100 21,463.80 88 22,505.42 39 23,170.54 4 24,677.62 20 25,570.07 14 26,418.15
Super User

Rather than creating a bunch of individual measures, one for each number range, you could create a calculation group, where each calculation item filters for a different range. That way you could drop both count and average measures onto the same visual with the calculation group and it will show all ranges at the same time

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors