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 want to calculate the AVERAGE Value of a category on the basis of the AVERAGE Value of the respective sub Category.
Category | Sub Category | Product | T10/NT10 | Value |
A | A1 | P1 | T10 | 3 |
A | A1 | P2 | T10 | 4 |
A | A1 | P3 | T10 | 5 |
A | A2 | P4 | T10 | 1 |
A | A2 | P5 | NT10 | 4 |
A | A2 | P6 | NT10 | 5 |
A | A3 | P7 | NT10 | 3 |
A | A3 | P8 | NT10 | 6 |
B | B3 | P9 | T10 | 1 |
B | B3 | P10 | NT10 | 4 |
B | B3 | P11 | T10 | 5 |
B | B3 | P12 | NT10 | 1 |
B | B1 | P13 | T10 | 4 |
B | B1 | P14 | T10 | 6 |
B | B1 | P15 | T10 | 7 |
I want the answer to be:-
When I filter T10/NT10 I will get the average of T10/NT10 like:-
T10 | 4 |
NT10 | 3.83 |
When I filter Individual Product I will get the individual value.
When I filter Sub Category, it will be the average of the Product like:-
A1 | 4 |
A2 | 3.33 |
A3 | 4.5 |
B3 | 4 |
B1 | 5.6 |
When I filter Category, it will be the average of the SubCategory within that Category like(A1+A2+A3)/3:
A | 3.94 |
B | 4.8 |
So when I filter T10 using a slicer I will get 4, I filter Category A then I will Get 3.94, I filter A1 I will get 4 and when I filter P1 I will get 3.
Kindly help me to formulate the DAX.
Thanks in Advance.
Solved! Go to Solution.
Please try this measure expression, replacing T1 for your actual table name.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please try this measure expression, replacing T1 for your actual table name.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@souvik900766 you want the average of averages? That's an almost meaningless calculation. Is this only when filtered by Product? What happens when if you filter by T10/NT10...is that just a simple average?
Hi,
If you filter on A, the answer should be 3.875 (simple average). I do not understand your logic.
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 |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |