Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
Need to calcuate Average on selected Value.
Product | Style No | Sale Qty | Avg Sales Qty | Sales Qty/ Avg Sales Qty |
CD SET | 1 | 30 | 6.26 | 4.79 |
CD SET | 2 | 28 | 6.26 | 4.47 |
CD SET | 3 | 14 | 6.26 | 2.24 |
CD SET | 4 | 13 | 6.26 | 2.08 |
CD SET | 5 | 11 | 6.26 | 1.76 |
CD SET | 6 | 9 | 6.26 | 1.44 |
CD SET | 7 | 8 | 6.26 | 1.28 |
CD SET | 8 | 7 | 6.26 | 1.12 |
CD SET | 9 | 7 | 6.26 | 1.12 |
CD SET | 10 | 6 | 6.26 | 0.96 |
CD SET | 11 | 6 | 6.26 | 0.96 |
CD SET | 12 | 5 | 6.26 | 0.80 |
CD SET | 13 | 5 | 6.26 | 0.80 |
CD SET | 14 | 4 | 6.26 | 0.64 |
CD SET | 15 | 4 | 6.26 | 0.64 |
CD SET | 16 | 4 | 6.26 | 0.64 |
CD SET | 17 | 4 | 6.26 | 0.64 |
CD SET | 18 | 4 | 6.26 | 0.64 |
CD SET | 19 | 4 | 6.26 | 0.64 |
CD SET | 20 | 3 | 6.26 | 0.48 |
CD SET | 21 | 3 | 6.26 | 0.48 |
CD SET | 22 | 3 | 6.26 | 0.48 |
CD SET | 23 | 3 | 6.26 | 0.48 |
CD SET | 24 | 3 | 6.26 | 0.48 |
CD SET | 25 | 3 | 6.26 | 0.48 |
CD SET | 26 | 3 | 6.26 | 0.48 |
CD SET | 27 | 3 | 6.26 | 0.48 |
CD SET | 28 | 3 | 6.26 | 0.48 |
CD SET | 29 | 3 | 6.26 | 0.48 |
CD SET | 30 | 2 | 6.26 | 0.32 |
CD SET | 31 | 2 | 6.26 | 0.32 |
CD SET | 32 | 2 | 6.26 | 0.32 |
CD SET | 33 | 2 | 6.26 | 0.32 |
CD SET | 34 | 2 | 6.26 | 0.32 |
@amitchauhan @bvanderwatt @ray_ux @tamerj1 @SpartaBI @v-chenyue-msft @mangaus1111
Hi All,
After using the ALLEXCEPT the value is not getting.
Result Wanted:
Hi @tamerj1 ,
Based on product i need the average.
Product | Size | Style No | Colour | Sale Qty | Avg Sales Qty | Sales Qty/ Avg Sales Qty |
Product01 | S | 1 | Red | 30 | 12.63 | 2.38 |
Product01 | M | 2 | Black | 28 | 12.63 | 2.22 |
Product01 | XL | 3 | Green | 14 | 12.63 | 1.11 |
Product01 | XXL | 4 | Red | 13 | 12.63 | 1.03 |
Product01 | S | 5 | Black | 11 | 12.63 | 0.87 |
Product01 | M | 6 | Green | 9 | 12.63 | 0.71 |
Product01 | XL | 7 | Red | 8 | 12.63 | 0.63 |
Product01 | XXL | 8 | Black | 7 | 12.63 | 0.55 |
Product01 | S | 9 | Green | 7 | 12.63 | 0.55 |
Product01 | M | 10 | Red | 6 | 12.63 | 0.48 |
Product01 | XL | 11 | Black | 6 | 12.63 | 0.48 |
Product02 | S | 12 | Green | 5 | 3.83 | 1.31 |
Product02 | M | 13 | Red | 5 | 3.83 | 1.31 |
Product02 | XL | 14 | Black | 4 | 3.83 | 1.04 |
Product02 | XXL | 15 | Green | 4 | 3.83 | 1.04 |
Product02 | S | 16 | Red | 4 | 3.83 | 1.04 |
Product02 | M | 17 | Black | 4 | 3.83 | 1.04 |
Product02 | XL | 18 | Green | 4 | 3.83 | 1.04 |
Product02 | XXL | 19 | Red | 4 | 3.83 | 1.04 |
Product02 | S | 20 | Black | 3 | 3.83 | 0.78 |
Product02 | M | 21 | Green | 3 | 3.83 | 0.78 |
Product02 | XL | 22 | Red | 3 | 3.83 | 0.78 |
Product02 | S | 23 | Black | 3 | 3.83 | 0.78 |
Product03 | XL | 24 | Green | 3 | 2.54 | 1.18 |
Product03 | XXL | 25 | Red | 3 | 2.54 | 1.18 |
Product03 | S | 26 | Black | 3 | 2.54 | 1.18 |
Product03 | M | 27 | Green | 3 | 2.54 | 1.18 |
Product03 | XL | 28 | Red | 3 | 2.54 | 1.18 |
Product03 | XXL | 29 | Black | 3 | 2.54 | 1.18 |
Product03 | S | 30 | Green | 2 | 2.54 | 0.79 |
Product03 | M | 31 | Red | 2 | 2.54 | 0.79 |
Product03 | XL | 32 | Black | 2 | 2.54 | 0.79 |
Product03 | M | 33 | Green | 2 | 2.54 | 0.79 |
Product03 | M | 34 | Red | 2 | 2.54 | 0.79 |
Product | Sales Qty | Avg Value |
Product01 | 139 | 12.63 |
Product02 | 46 | 3.83 |
Product03 | 28 | 2.54 |
FYI.
Table1 | Table1 | Table1 | Table1 | Table2 | Based on Product I need the Avg (Result Required) |
Product | Size | Style No | Colour | Sale Qty | Avg Sales Qty |
Product01 | S | 1 | Red | 30 | 12.63 |
Product01 | M | 2 | Black | 28 | 12.63 |
Product01 | XL | 3 | Green | 14 | 12.63 |
Product01 | XXL | 4 | Red | 13 | 12.63 |
Product01 | S | 5 | Black | 11 | 12.63 |
Product01 | M | 6 | Green | 9 | 12.63 |
Product01 | XL | 7 | Red | 8 | 12.63 |
Product01 | XXL | 8 | Black | 7 | 12.63 |
Product01 | S | 9 | Green | 7 | 12.63 |
Product01 | M | 10 | Red | 6 | 12.63 |
Product01 | XL | 11 | Black | 6 | 12.63 |
Product02 | S | 12 | Green | 5 | 3.83 |
Product02 | M | 13 | Red | 5 | 3.83 |
Product02 | XL | 14 | Black | 4 | 3.83 |
Product02 | XXL | 15 | Green | 4 | 3.83 |
Product02 | S | 16 | Red | 4 | 3.83 |
Product02 | M | 17 | Black | 4 | 3.83 |
Product02 | XL | 18 | Green | 4 | 3.83 |
Product02 | XXL | 19 | Red | 4 | 3.83 |
Product02 | S | 20 | Black | 3 | 3.83 |
Product02 | M | 21 | Green | 3 | 3.83 |
Product02 | XL | 22 | Red | 3 | 3.83 |
Product02 | S | 23 | Black | 3 | 3.83 |
Product03 | XL | 24 | Green | 3 | 2.54 |
Product03 | XXL | 25 | Red | 3 | 2.54 |
Product03 | S | 26 | Black | 3 | 2.54 |
Product03 | M | 27 | Green | 3 | 2.54 |
Product03 | XL | 28 | Red | 3 | 2.54 |
Product03 | XXL | 29 | Black | 3 | 2.54 |
Product03 | S | 30 | Green | 2 | 2.54 |
Product03 | M | 31 | Red | 2 | 2.54 |
Product03 | XL | 32 | Black | 2 | 2.54 |
Product03 | M | 33 | Green | 2 | 2.54 |
Product03 | M | 34 | Red | 2 | 2.54 |
@arvindarvind24
How are table1 and Table2 connected? Are looking for a measure or a calculated column?
@tamerj1 There are connected through the icode relationship and am looking for measure.
@arvindarvind24
However, please try
Avg Sales Qty =
CALCULATE (
AVERAGE ( Table2[Sales Qty] ),
ALLEXCEPT ( Table1, Table1[Produvt] )
)
User | Count |
---|---|
65 | |
46 | |
20 | |
18 | |
15 |
User | Count |
---|---|
120 | |
41 | |
40 | |
28 | |
23 |