cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## measure to average by a group of 2 columns

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

1 ACCEPTED SOLUTION
Helper II

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

9 REPLIES 9
Super User

@Pillic change my measure to this:

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

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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

Hi,

This measure works

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

@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] )
)
``````

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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Community Champion

Reasonable idea,

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

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!
Community Champion
``````AVG =
CALCULATE(
AVERAGE( 'table'[weight] ),
ALLEXCEPT( 'table', 'table'[Plant], 'table'[product] )
)``````

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!
Super User

@Pillic try this measure:

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

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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper II

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

Helper II

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.

Helper II

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

Announcements