The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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 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.⚡
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.
Hi,
This measure works
@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.⚡
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.
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! |
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! |
@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.⚡
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.
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
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.
This is the original view of the table in the report:
Index = Product, fck comes from a referenced tabel with this measure
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
136 | |
101 | |
101 | |
72 | |
58 |
User | Count |
---|---|
263 | |
121 | |
114 | |
93 | |
85 |