Forum Discussion
Average of Filtered Values
I am wanting to create a new column based on the total number of filtered EstimateNames divided by the sum of each of their respective unit costs. So in the example below, there are 3 estimates selected based on the filter, so in my new column, A Substructure should be 7.25 = (9.71 + 8 + 4.03) / 3.
Hi rlmeyer ,
1.My sample data is this.
WBS Breakdown
EstimateName
Estimate
A SUBSTRUCTURE
Cost Per Unit1
9.71
B SHELL
Cost Per Unit1
20.09
C INTERIORS
Cost Per Unit1
43.96
D SERVICES
Cost Per Unit1
37.13
E EQUIPMENT AND FURNISHINGS
Cost Per Unit1
8.29
F SPECIAL CONSTRUCTION AND DEMOLITION
Cost Per Unit1
1.74
G SITEWORK
Cost Per Unit1
9.59
Z GENRAL
Cost Per Unit1
29.42
A SUBSTRUCTURE
Cost Per Unit2
8
B SHELL
Cost Per Unit2
34.26
C INTERIORS
Cost Per Unit2
62.3
D SERVICES
Cost Per Unit2
57.01
E EQUIPMENT AND FURNISHINGS
Cost Per Unit2
9.82
F SPECIAL CONSTRUCTION AND DEMOLITION
Cost Per Unit2
0.04
G SITEWORK
Cost Per Unit2
12.41
Z GENRAL
Cost Per Unit2
38.18
A SUBSTRUCTURE
Cost Per Unit3
4.03
B SHELL
Cost Per Unit3
22.64
C INTERIORS
Cost Per Unit3
41.23
D SERVICES
Cost Per Unit3
39.36
E EQUIPMENT AND FURNISHINGS
Cost Per Unit3
9.74
F SPECIAL CONSTRUCTION AND DEMOLITION
Cost Per Unit3
3.12
G SITEWORK
Cost Per Unit3
16.21
Z GENRAL
Cost Per Unit3
22.89
2.Create a measure like this. You can refer to HASONEVALUE function and DINTINCTCOUNT function.
Measure = IF ( HASONEVALUE ( 'Table'[EstimateName] ), SUM ( 'Table'[Estimate] ), DIVIDE ( SUM ( 'Table'[Estimate] ), DISTINCTCOUNT ( 'Table'[EstimateName] ) ) )3.Create a slicer with EstimateName column and a matrix. The red box means to rename the Column subtotals label.
4.When you filter from the slicer, the average value of the matrix will change.
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
rlmeyer Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Hi rlmeyer ,
1.My sample data is this.
WBS Breakdown
EstimateName
Estimate
A SUBSTRUCTURE
Cost Per Unit1
9.71
B SHELL
Cost Per Unit1
20.09
C INTERIORS
Cost Per Unit1
43.96
D SERVICES
Cost Per Unit1
37.13
E EQUIPMENT AND FURNISHINGS
Cost Per Unit1
8.29
F SPECIAL CONSTRUCTION AND DEMOLITION
Cost Per Unit1
1.74
G SITEWORK
Cost Per Unit1
9.59
Z GENRAL
Cost Per Unit1
29.42
A SUBSTRUCTURE
Cost Per Unit2
8
B SHELL
Cost Per Unit2
34.26
C INTERIORS
Cost Per Unit2
62.3
D SERVICES
Cost Per Unit2
57.01
E EQUIPMENT AND FURNISHINGS
Cost Per Unit2
9.82
F SPECIAL CONSTRUCTION AND DEMOLITION
Cost Per Unit2
0.04
G SITEWORK
Cost Per Unit2
12.41
Z GENRAL
Cost Per Unit2
38.18
A SUBSTRUCTURE
Cost Per Unit3
4.03
B SHELL
Cost Per Unit3
22.64
C INTERIORS
Cost Per Unit3
41.23
D SERVICES
Cost Per Unit3
39.36
E EQUIPMENT AND FURNISHINGS
Cost Per Unit3
9.74
F SPECIAL CONSTRUCTION AND DEMOLITION
Cost Per Unit3
3.12
G SITEWORK
Cost Per Unit3
16.21
Z GENRAL
Cost Per Unit3
22.89
2.Create a measure like this. You can refer to HASONEVALUE function and DINTINCTCOUNT function.
Measure = IF ( HASONEVALUE ( 'Table'[EstimateName] ), SUM ( 'Table'[Estimate] ), DIVIDE ( SUM ( 'Table'[Estimate] ), DISTINCTCOUNT ( 'Table'[EstimateName] ) ) )3.Create a slicer with EstimateName column and a matrix. The red box means to rename the Column subtotals label.
4.When you filter from the slicer, the average value of the matrix will change.
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
