Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |