March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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,
Solved! Go to 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
)
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.
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 )
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:
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?
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:
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
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.
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:
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 |
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
26 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
40 | |
27 | |
27 | |
21 | |
19 |