Getting the sum after an average measure

[goalie_]

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

[v-luwang-msft · Accepted solution]

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

[Vera_33]

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. 

[Vera_33]

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