Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Tan_LC
Helper I
Helper I

Average in DAX

Dear all,

 

In below table, I would like to calculate the Average using DAX and the desired result should be same as "Average in Excel".

However, when doing calculation in DAX Power BI, the result turns to be abnormal which giving me a single digit number instead of 4 digits numeric (as shown in "Average in DAX").

Please help.

 

Line1: S2: P3: MAverage in ExcelAverage in DAX
C12,0122,3472,2612,2077.13
C21,7961,9391,9341,8906.76
C32,1492,2422,2162,2028.69

 

Thank you.

 

Regards,

Lee Cheng

1 ACCEPTED SOLUTION

Hi, @Tan_LC 

 

You can try the following methods.

Measure = 
Var _N1=CALCULATE(COUNT('Table'[Line]),ALLEXCEPT('Table','Table'[Shift],'Table'[Line]))
Var _N2=CALCULATE(DISTINCTCOUNT('Table'[Shift]))
Return
DIVIDE(_N1,_N2)

vzhangti_0-1676539316036.png

vzhangti_1-1676539325275.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-zhangti
Community Support
Community Support

Hi, @Tan_LC 

 

You can try the following methods.

In the Power Query, Transpose-Unpivot Columns

vzhangti_0-1676339991947.png

vzhangti_1-1676340049211.png

Column = CALCULATE(AVERAGE('Table'[Value]),ALLEXCEPT('Table','Table'[Line]))

vzhangti_2-1676340186507.png

Or

Measure = CALCULATE(AVERAGE('Table'[Value]),ALLEXCEPT('Table','Table'[Line]))

vzhangti_3-1676340216048.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-zhangti 

 

In below data, would appreciate if you can advise on the formula to get the Ave using DAX for Line.

I've tried on your earlier formula, if there are new columns such as Date, Machine No and & Part No added, the Ave result becomes inaccurate. 

 

DateLineShiftMachine No.Part No.Output
12/2/2023C1M101HF1354
12/2/2023C1M101HF2375
12/2/2023C1M101LN0055
12/2/2023C1P101HF1355
12/2/2023C1P101HF2376
12/2/2023C1S101HF1354
12/2/2023C1S101HF2374
12/2/2023C1S101LN0054
13/2/2023C1M101HF1353
13/2/2023C1M101HF2374
13/2/2023C1M101LN0054
13/2/2023C1P101HF1352
13/2/2023C1P101HF2374
13/2/2023C1S101LN0053
13/2/2023C1S101HF1354
13/2/2023C1S101HF2375
12/2/2023C2M201HF3286
12/2/2023C2M201LN0036
12/2/2023C2P201HF3287
12/2/2023C2P201LN0037
12/2/2023C2S201HF32810
13/2/2023C2M201LN0032
13/2/2023C2M201HF3284
13/2/2023C2P201HF3283
13/2/2023C2P201LN0033
12/2/2023C3M301AB0032
12/2/2023C3M301AB0844
12/2/2023C3P301AB0843
12/2/2023C3P301AB0037
12/2/2023C3P301AB0958
12/2/2023C3S301AB0038
13/2/2023C3M301AB0031
13/2/2023C3M301AB0843
13/2/2023C3P301AB0841
13/2/2023C3P301AB0952
13/2/2023C3S301AB0037

 

 

Thanks.

@v-zhangti 

Thanks on the solution given but it still giving me the incorrect outcome. Perhaps, you can have a look on the raw data I reply to @ERD  for details.

Thanks.

ERD
Super User
Super User

@Tan_LC , how does your data look like and the measure that gives wrong results?

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

@ERD 

I extracted some of the data and share with you as below:

In the Part ID column, each ID is an unique ID and each row represents 1 count.

In the DAX Average calculation, it should reflect correct Ave in accordance with the filter I select regardless of Line, Part ID, Shift or Date. 

 

LineSPMAve in Excel
C124172522
C210201816
C315211015

 

Machine
No.
LinePart No.Part IDShiftDate
101C1HF13532D5S12/2/2023
101C1HF13532D6S12/2/2023
101C1HF13532D7S12/2/2023
101C1HF13532D8S12/2/2023
101C1HF13532DHS13/2/2023
101C1HF13532DIS13/2/2023
101C1HF13532DJS13/2/2023
101C1HF13532DKS13/2/2023
101C1HF13532EEP12/2/2023
101C1HF13532EFP12/2/2023
101C1HF13532EMP13/2/2023
101C1HF13532EOP13/2/2023
101C1HF13532ESP12/2/2023
101C1HF13532ETP12/2/2023
101C1HF13532EUP12/2/2023
101C1HF13532HBM12/2/2023
101C1HF13532HCM12/2/2023
101C1HF13532HDM12/2/2023
101C1HF13532HEM12/2/2023
101C1HF13532HLM13/2/2023
101C1HF13532HMM13/2/2023
101C1HF13532HNM13/2/2023
101C1HF2370D0WS12/2/2023
101C1HF2370D0XS12/2/2023
101C1HF2370D0YS12/2/2023
101C1HF2370D0ZS13/2/2023
101C1HF2370D10S13/2/2023
101C1HF2370D11S13/2/2023
101C1HF2370D12S13/2/2023
101C1HF2370D13S13/2/2023
101C1HF2370D1BS12/2/2023
101C1HF2370D1PP12/2/2023
101C1HF2370D1QP12/2/2023
101C1HF2370D1RP12/2/2023
101C1HF2370D1SP12/2/2023
101C1HF2370D1TP12/2/2023
101C1HF2370D1UP12/2/2023
101C1HF2370D20P13/2/2023
101C1HF2370D21P13/2/2023
101C1HF2370D22P13/2/2023
101C1HF2370D23P13/2/2023
101C1HF2370D7JM12/2/2023
101C1HF2370D7MM12/2/2023
101C1HF2370D7NM12/2/2023
101C1HF2370D7OM12/2/2023
101C1HF2370D7PM12/2/2023
101C1HF2370D7QM13/2/2023
101C1HF2370D7RM13/2/2023
101C1HF2370D7XM13/2/2023
101C1HF2370D7YM13/2/2023
101C1LN00529I3S12/2/2023
101C1LN00529I4S12/2/2023
101C1LN00529I5S12/2/2023
101C1LN00529IDS13/2/2023
101C1LN00529IES13/2/2023
101C1LN00529IKS13/2/2023
101C1LN00529ILS12/2/2023
101C1LN00529NXM12/2/2023
101C1LN00529NYM12/2/2023
101C1LN00529NZM12/2/2023
101C1LN00529O0M12/2/2023
101C1LN00529O1M12/2/2023
101C1LN00529OBM13/2/2023
101C1LN00529OCM13/2/2023
101C1LN00529ODM13/2/2023
101C1LN00529OEM13/2/2023
201C2HF32800T8S12/2/2023
201C2HF32800T9S12/2/2023
201C2HF32800TAS12/2/2023
201C2HF32800TBS12/2/2023
201C2HF32800TCS12/2/2023
201C2HF32800TDS12/2/2023
201C2HF32800TJS12/2/2023
201C2HF32800TKS12/2/2023
201C2HF32800TLS12/2/2023
201C2HF32800TMS12/2/2023
201C2HF32800U1P12/2/2023
201C2HF32800U2P12/2/2023
201C2HF32800U3P12/2/2023
201C2HF32800UPP12/2/2023
201C2HF32800UQP12/2/2023
201C2HF32800URP13/2/2023
201C2HF32800V6P13/2/2023
201C2HF32800V7P13/2/2023
201C2HF32800V8P12/2/2023
201C2HF32800V9P12/2/2023
201C2HF32800W5M12/2/2023
201C2HF32800W6M12/2/2023
201C2HF32800W7M12/2/2023
201C2HF32800W8M12/2/2023
201C2HF32800W9M12/2/2023
201C2HF32800WAM13/2/2023
201C2HF32800WBM13/2/2023
201C2HF32800WCM13/2/2023
201C2HF32800WDM13/2/2023
201C2HF32800WEM12/2/2023
201C2LN0032MQBP12/2/2023
201C2LN0032MQCP12/2/2023
201C2LN0032MQDP12/2/2023
201C2LN0032MQEP12/2/2023
201C2LN0032MQFP12/2/2023
201C2LN0032MQGP13/2/2023
201C2LN0032MQHP13/2/2023
201C2LN0032MQIP13/2/2023
201C2LN0032MQUP12/2/2023
201C2LN0032MQVP12/2/2023
201C2LN0032MT8M12/2/2023
201C2LN0032MT9M12/2/2023
201C2LN0032MTAM12/2/2023
201C2LN0032MTGM13/2/2023
201C2LN0032MTHM13/2/2023
201C2LN0032MTSM12/2/2023
201C2LN0032MTTM12/2/2023
201C2LN0032MTUM12/2/2023
301C3AB0031P44S12/2/2023
301C3AB0031P45S12/2/2023
301C3AB0031P46S12/2/2023
301C3AB0031P47S12/2/2023
301C3AB0031P48S13/2/2023
301C3AB0031P49S13/2/2023
301C3AB0031P4AS13/2/2023
301C3AB0031P4BS13/2/2023
301C3AB0031P4CS13/2/2023
301C3AB0031P4DS13/2/2023
301C3AB0031P4ES13/2/2023
301C3AB0031P4FS12/2/2023
301C3AB0031P4GS12/2/2023
301C3AB0031P4HS12/2/2023
301C3AB0031P4IS12/2/2023
301C3AB0031P5YP12/2/2023
301C3AB0031P5ZP12/2/2023
301C3AB003TP60P12/2/2023
301C3AB003TP61P12/2/2023
301C3AB0031P86P12/2/2023
301C3AB0031P87P12/2/2023
301C3AB0031P88P12/2/2023
301C3AB0031P8HM13/2/2023
301C3AB0031P8IM12/2/2023
301C3AB0031P8JM12/2/2023
301C3AB084233JP12/2/2023
301C3AB084233KP13/2/2023
301C3AB084233LP12/2/2023
301C3AB084233MP12/2/2023
301C3AB084235FM12/2/2023
301C3AB084235GM12/2/2023
301C3AB084235LM13/2/2023
301C3AB084235NM13/2/2023
301C3AB084235OM13/2/2023
301C3AB084235PM12/2/2023
301C3AB084235RM12/2/2023
301C3AB0950R66P12/2/2023
301C3AB0950R67P12/2/2023
301C3AB0950R6CP13/2/2023
301C3AB0950R6KP13/2/2023
301C3AB0950R6LP12/2/2023
301C3AB0950R6MP12/2/2023
301C3AB0950R6NP12/2/2023
301C3AB0950R6OP12/2/2023
301C3AB0950R6YP12/2/2023
301C3AB0950R6ZP12/2/2023

 

Thanks.

Hi, @Tan_LC 

 

You can try the following methods.

Measure = 
Var _N1=CALCULATE(COUNT('Table'[Line]),ALLEXCEPT('Table','Table'[Shift],'Table'[Line]))
Var _N2=CALCULATE(DISTINCTCOUNT('Table'[Shift]))
Return
DIVIDE(_N1,_N2)

vzhangti_0-1676539316036.png

vzhangti_1-1676539325275.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

do not really get your point, just guess

=AVERAGEX(SUMMARIZE('Table3','Table3'[Line],'Table3'[Shift]),CALCULATE(DISTINCTCOUNT(Table3[Part ID])))

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.