measure to average by a group of 2 columns

[Pillic]

Hi,

I have the following table:

Plant	product	weight
H001	AA	10
H001	BB	11
H002	AA	15
H002	AA	11
H002	CC	14

How can the average weight grouped by pant and product being calculated in a measure and shown in each row?

Plant	product	weight	average by plant&product
H001	AA	10	10
H001	BB	11	11
H002	AA	15	13
H002	AA	11	13
H002	CC	14	14

I am struggeling to add an additional filter to the below measure. It shows all averages per plant but missing the product.

Measure = CALCULATE (
    AVERAGE (table[weight]),
    FILTER ( ALLSELECTED (table), table[Plant]  = MAX (table[Plant]))
 )

 How can I add the missing product to the measure?

Kind Regards

[parry2k]

@Pillic change my measure to this:

Measure = 
CALCULATE (
    AVERAGE ( Plant[weight] ),
    ALLSELECTED ( Plant ),
    ALLSELECTED ( 'Product' ),
    VALUES ( Plant[Plant] ),
    VALUES ( 'Product'[product] )
)

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Ashish_Mathur]

Hi,

This measure works

[parry2k]

@Pillic I will not use ALLEXCEPT in case you want to apply slice the data and want to calculate average on selected records. I will update my previous measure like this:

Measure = 
CALCULATE (
    AVERAGE ( Plant[weight] ),
    ALLSELECTED ( Plant ),
    VALUES ( Plant[product] ),
    VALUES ( plant[plant] )
)

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[CNENFRNL]

Reasonable idea,

AVG =
CALCULATE(
    AVERAGE( 'table'[weight] ),
    SUMMARIZE( ALLSELECTED( 'table' ), 'table'[Plant], 'table'[product] )
)

[CNENFRNL]

AVG = 
CALCULATE(
    AVERAGE( 'table'[weight] ),
    ALLEXCEPT( 'table', 'table'[Plant], 'table'[product] )
)

[parry2k]

@Pillic try this measure:

Measure = CALCULATE (
    AVERAGE (Plant[weight]),
    ALL(Plant),
    VALUES(Plant[product]),
    values(plant[plant])
)

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Pillic · Accepted solution]

for any reason this measure works:

Measure = 
CALCULATE(
    AVERAGEX( SUMMARIZE( table,table[product],table[value]), table[value] ),
    ALLSELECTED( table[value])
)

So I will go with this...but Thanks for pointing me to the right direction, although in a later calculation with this measure I get strange results. I will open a new thread if needed.

Thanks all 

[Pillic]

@Ashish_Mathur @parry2k 

I need to appologize, as indeed this table is already filtered by slicers.

The original table source looks like this with more details:

Plant	product	Details	Value
H001	AA	weight	10
H001	BB	weight	11
H002	AA	weight	15
H002	AA	weight	11
H002	CC	weight	14
H001	BB	density	2350
H002	AA	density	2340

And the slicers will go for the plant and the detail so the filtered table as visual looks like this:

Plant	product	Value	desired measure
H001	AA	10
H001	BB	11
H002	AA	15
H002	AA	11
H002	CC	14

So the ALLEXCEPT approach calculates the AVERAGE of all details by plant and for any reason if I use the ALLSELECTED measure it is a "copy" of the column Value.

[Pillic]

This is the original view of the table in the report:

Index = Product, fck comes from a referenced tabel with this measure 

fck = AVERAGEX(TestResults, RELATED(Gesamtliste[Festigkeitsklasse])) , Prüfung = Detail, Wert = Value

Only the 1st 2 rows are working with the measure with ALLSELECTED. In the highlighted rows should be the result 45,73 for each highlighted row.

Am I missing something or made a wrong reference?

There is a 1:n relationship from Gesamtliste (where detailed informations for each product is unique) to the Testresults with multiple plants, products, Tests and Testresults.

Thanks in avdance for having a look at this