topic Re: Averageifs and Countifs in Power Bi in DAX Commands and Tips

Averageifs and Countifs in Power Bi

Anonymous — Thu, 11 Aug 2022 20:12:49 GMT

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,

Re: Averageifs and Countifs in Power Bi

johnt75 — Fri, 12 Aug 2022 13:13:46 GMT

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 )

Re: Averageifs and Countifs in Power Bi

Anonymous — Mon, 15 Aug 2022 19:53:15 GMT

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

Re: Averageifs and Countifs in Power Bi

johnt75 — Tue, 16 Aug 2022 08:16:42 GMT

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

Re: Averageifs and Countifs in Power Bi

Anonymous — Fri, 19 Aug 2022 21:30:02 GMT

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 )

Re: Averageifs and Countifs in Power Bi

johnt75 — Mon, 22 Aug 2022 08:09:08 GMT

Each one would need to be its own calculation item

Re: Averageifs and Countifs in Power Bi

Anonymous — Tue, 23 Aug 2022 17:23:57 GMT

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)

Re: Averageifs and Countifs in Power Bi

johnt75 — Wed, 24 Aug 2022 08:42:04 GMT

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

Re: Averageifs and Countifs in Power Bi

Anonymous — Wed, 24 Aug 2022 17:09:59 GMT

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?

Re: Averageifs and Countifs in Power Bi

johnt75 — Thu, 25 Aug 2022 08:10:35 GMT

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

Re: Averageifs and Countifs in Power Bi

Anonymous — Thu, 25 Aug 2022 14:14:51 GMT

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

)

Re: Averageifs and Countifs in Power Bi

johnt75 — Thu, 25 Aug 2022 14:26:41 GMT

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.