Averageifs and Countifs in Power Bi

[Kikemx90]

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,

[johnt75 · Accepted solution]

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
)

[Kikemx90]

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 )

[johnt75]

Each one would need to be its own calculation item

[Kikemx90]

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)

[johnt75]

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

[Kikemx90]

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?

[johnt75]

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

[Kikemx90]

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

)

[johnt75 · Accepted solution]

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 · Accepted solution]

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
)

[Kikemx90]

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

[johnt75]

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