Forum Discussion
Helper IIIGetting the sum after an average measure
How would I create a measure for a "Quantity" that takes the sum between selected "Description" selected after it takes the average between "Spec" for a given ID?
I'd like to have a table that looks like if mat1 and mat2 are selected in a slicer, I'd want a table visual that looks like
| ID | quantity |
| A | 0.53 |
| B | 0.58 |
| C | 0.33 |
or if mat5 or mat6 are selected
| ID | quantity |
| A | 0.18 |
| B | 0.12 |
| C | 0 |
My data is structured as below:
| Request | ID | description | quantity |
| 1 | A | mat1 | 0.1 |
| 1 | A | mat2 | 0.43 |
| 1 | A | mat3 | 0.21 |
| 1 | A | mat4 | 0.08 |
| 1 | A | mat5 | 0.12 |
| 1 | A | mat6 | 0.06 |
| 1 | B | mat1 | 0.25 |
| 1 | B | mat2 | 0.33 |
| 1 | B | mat3 | 0.2 |
| 1 | B | mat4 | 0.1 |
| 1 | B | mat5 | 0.12 |
| 1 | C | mat1 | 0.26 |
| 1 | C | mat2 | 0.07 |
| 1 | C | mat3 | 0.63 |
| 1 | C | mat4 | 0.04 |
| 2 | A | mat1 | 0.1 |
| 2 | A | mat2 | 0.43 |
| 2 | A | mat3 | 0.21 |
| 2 | A | mat4 | 0.08 |
| 2 | A | mat5 | 0.12 |
| 2 | A | mat6 | 0.06 |
| 2 | B | mat1 | 0.25 |
| 2 | B | mat2 | 0.33 |
| 2 | B | mat3 | 0.2 |
| 2 | B | mat4 | 0.1 |
| 2 | B | mat5 | 0.12 |
| 2 | C | mat1 | 0.26 |
| 2 | C | mat2 | 0.07 |
| 2 | C | mat3 | 0.63 |
| 2 | C | mat4 | 0.04 |
Hi goalie_ ，
Try the measure like below:
sum2 = CALCULATE ( SUM ( 'Table'[quantity] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[ID] = MAX ( 'Table'[ID] ) ) )/DISTINCTCOUNT('Table'[Request])Wish it is helpful for you!
Best Regards
Lucien
4 Replies
Hi goalie_
While mat5 or mat6 are selected, ID B only has mat5, but you need to divide 2 (mat5 or mat6)? I am using a connected dim table called slicerD which only conatins description
sum of quantity = COALESCE(DIVIDE( SUM(yourTable[quantity]),COUNTROWS(VALUES(slicerD[description])),0),0)
goalie_What do you mean by a connected dimtable? And yes ID B is only meant to contain upto mat5. For a given ID, the number of mats are not always identical.
Hi goalie_
I mean the column in Slicer is not in the same table, but another connected one. The logic how you calculate affects the way you write your measure
- v-luwang-msft
Community Support
Hi goalie_ ，
Try the measure like below:
sum2 = CALCULATE ( SUM ( 'Table'[quantity] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[ID] = MAX ( 'Table'[ID] ) ) )/DISTINCTCOUNT('Table'[Request])Wish it is helpful for you!
Best Regards
Lucien
