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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Kikemx90
Frequent Visitor

Averageifs and Countifs in Power Bi

Kikemx90_0-1660248547987.pngKikemx90_1-1660248569914.pngKikemx90_2-1660248598493.png

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

View solution in original post

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.

View solution in original post

11 REPLIES 11
Kikemx90
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:

 

Kikemx90_0-1660944584916.png

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 )

 

Each one would need to be its own calculation item

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)

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

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?

Kikemx90_0-1661360986775.png

 

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

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:

Kikemx90_0-1661436799516.png

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
)

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.

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

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

 

Kikemx90_0-1660593142197.png

I need it like excel:

 

VOLCOST
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

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.