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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.