cancel
Showing results for
Did you mean:
Regular Visitor

## Average for 3 terms in a row

Hi everyone,

Couldn't get the average for a few terms in a row. PBI is giving me a value for each row, but i need the average for 3 terms in a row. Help me please guys?!

1 ACCEPTED SOLUTION

Accepted Solutions
Regular Visitor

## Re: Average for 3 terms in a row

Hi, Dale.

How did you define the encircled parameters, I mean is it a separate function or what? Am I supposed to create them separately as a function and after put in the formula above?

27 REPLIES 27
Community Support Team

## Re: Average for 3 terms in a row

Hi @Rustami4,

Can you please provide a sample in TEXT mode? The .pbix file would be great.

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Average for 3 terms in a row

Hi Dale!

Here is in the mode text.. I would also send you a whole pbix file, but this is a kind of confidential information, i'm not allowed to do it  Sorry

The point is I'm trying to get an average value of METERS (column#10) for every ASSY (column#4), but every Assy may invcludes a few SERIALs (column#5), and a serial involves number of REPAIRs (column#3) and all of those repairs may include many rows sometimes what makes this task complicated. The Hierarchy is the following (from the left to right): Assy--Serials--Repair--Rows... and PBI counts an average for every row being within one Assy, But the task is to determine an average for every ASSY providing by every number of REPAIR. In another words we could do according to the following formula: Summarize all meters for every ASSY in dependance of REPAIR number divided into the number of SERIALs those correspond to ASSY.

 Bit size,mm Bit Type Repair# Assy# Serial# Р IH date Р РћРћH date Interval,in Interval out Meters RРћР ,m/h 393,7 R519SDHXU new A05958LC 9000852 02.09.2016 02.09.2016 10 50 40 95,23809524 295,3 R519SDHXU R1 A20189 9000459 02.09.2016 03.09.2016 50 983 933 60,86105675 215,9 VS516DG1HXU new A20783 9001116 05.09.2016 08.09.2016 983 2485 1502 49,50560316 215,9 VM513DGHX new A20062 9001068 09.09.2016 11.09.2016 2485 3152 667 28,370906 393,7 R519SDHXU new A05958LC 6013913 31.05.2016 02.06.2016 67 522 455 44,3902439 295,3 R619SDHXU R1 A05914 9000778 03.06.2016 12.06.2016 522 1339 817 14,59189141 220,7 VM416DGHX new A04892 6008451 14.06.2016 21.06.2016 1339 2596 1257 34,83924612 220,7 HE14MRSV roller cone 8792 1383048 08.01.2016 09.01.2016 959 960 1 12,5 220,7 V613DGHX R1 A20061 9000677 09.01.2016 21.01.2016 960 2809 1849 23,86114337 220,7 V613DGHX R1 A20061 9000677 21.01.2016 26.01.2016 2809 3210 401 16,42096642 220,7 V613DGHX R1 A20070 9000824 31.01.2016 06.02.2016 2836 3176 340 12,71503366 142,9 V613DG1X R1 A05894 9000340 16.02.2016 22.02.2016 3176 3720 544 18,71345029 393,7 R519SDHXU new A05958LC 9000852 17.08.2016 17.08.2016 10 50 40 53,33333333 295,3 R519SDHXU R1 A20189 9000459 17.08.2016 19.08.2016 50 1002 952 55,73770492 215,9 VS516DG1HXU new A20783 9001209 23.08.2016 28.08.2016 1002 3199 2197 51,69411765 393,7 R519SDHXU new A05958LC 9000852 16.07.2016 16.07.2016 10 47 37 123,3333333 295,3 R519SDHXU R1 A20189 9000459 16.07.2016 18.07.2016 47 1027 980 61,51914626 155,6 VS416DGH new A20768 9001096 02.08.2016 06.08.2016 3395 4040 645 30,14018692 393,7 R519SDHXU new A05958LC 9000852 25.04.2016 25.04.2016 12 48 36 62,06896552 295,3 VM516DG1HX R1 A20296 9000749 25.04.2016 28.04.2016 48 978 930 55,12744517 215,9 V613SG1X new A20337LC 9000774 02.05.2016 06.05.2016 978 3007 2029 43,91774892 215,9 V613DG1HX new A20337LC 9000718 11.05.2016 12.05.2016 3037 3132 95 41,30434783 490 L111G roller cone R019458 1497615 25.06.2016 26.06.2016 0 61 61 22,18181818 393,7 R616SDGHXU R1 A05955 6014595 26.06.2016 29.06.2016 61 508 447 37,25 295,3 R619SDHXU R1 A05914 9000778 01.07.2016 06.07.2016 508 1275 767 25,14754098 220,7 VM416DGHX R2 A04892 6008451 08.07.2016 16.07.2016 1275 2601 1326 25,5 393,7 R519SDHXU new A05958LC 9000852 03.04.2016 03.04.2016 12 46 34 68 215,9 V613SG1X new A20337LC 9000774 09.04.2016 15.04.2016 989 3169 2180 45,27518172 393,7 R519SDHXU new A05958LC 9000852 18.06.2016 18.06.2016 10 50 40 80 295,3 R519SDHXU R1 A20189 9000459 18.06.2016 20.06.2016 50 1033 983 65,18567639 155,6 VM416DG new A04920 6008992 04.07.2016 12.07.2016 3436 4418 982 18,88098443 393,7 HR1GJMRS roller cone 8808 1382404 26.05.2016 28.05.2016 0 65 65 13 393,7 VTD519SDHXU R1 A05216 6010207 28.05.2016 03.06.2016 65 555 490 28,67173786 295,3 R519SDHX R5 A05962LC 6015327 13.06.2016 26.06.2016 555 1067 512 26,47362978 295,3 R519SDHX R5 A05962LC 6015327 26.06.2016 29.06.2016 1067 1398 331 23,22807018 295,3 R519SDHX R5 A05962LC 6015327 29.06.2016 02.07.2016 1398 1653 255 26,15384615 215,9 V613SG1X new A20337LC 9000874 13.07.2016 21.07.2016 1654 2332 678 13,51405222 215,9 V613SG1X new A20337LC 9000874 22.07.2016 23.07.2016 2332 2346 14 9,85915493 215,9 V613SG1X new A20337LC 9000874 25.07.2016 26.07.2016 2368 2382 14 8 215,9 V613SG1X new A20337LC 9000874 29.07.2016 30.07.2016 2442 2453 11 4,247104247 215,9 V613SG1X new A20337LC 9000874 03.08.2016 03.08.2016 2538 2550 12 16 215,9 V613SG1X new A20337LC 9000874 04.08.2016 04.08.2016 2550 2590 40 10 215,9 V613SG1X new A20337LC 9000874 05.08.2016 06.08.2016 2590 2615 25 20 215,9 V613SG1X new A20337LC 9000874 08.08.2016 09.08.2016 2639 2669 30 4,231311707 215,9 V613SG1X new A20337LC 9000874 10.08.2016 11.08.2016 2674 2706 32 8,533333333 215,9 V613SG1X new A20337LC 9000874 12.08.2016 13.08.2016 2724 2770 46 4,679552391
Community Support Team

## Re: Average for 3 terms in a row

Hi @Rustami4,

DAX responses to the context dynamically. I think a simple formula is enough. Do you want it in a visual or in the table? In other words, do you want it to be a calculated column or a measure? You can try it in this file.

```average =
AVERAGE ( Table1[Meters] )```

If this isn't the solution, can you please make an example?

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Average for 3 terms in a row

Hi Dale!

None of the formula above hadn't solved it. Maybe you didnt get the whole idea. I'll try to explain it differently using an example

Let's look at the example below... There is what I'm trying to define..We need to summarize all the same serial's values and determine an average value for summarized serials within one ASSY

 ASSY Serials ## Total NEW R1 R2 R3 Рђ04892 6008451 1st run 1000 800 600 400 6008451 2nd run 1100 900 700 600 Sum for serial 6100 2100 1700 1300 1000 9000020 1st run 2000 1800 1500 1200 9000020 2nd run 1400 1200 1000 200 Sum for serial 10300 3400 3000 2500 1400 9000010 1st run 2300 2000 1600 1200 9000010 2nd run 1800 1600 1400 1200 Sum for serial 13100 4100 3600 3000 2400 AVERAGE FOR ASSY 3200,00 2766,67 2266,67 1600,00
Regular Visitor

## Re: Average for 3 terms in a row

That's not actualy clearly in teхt mode. Look at this

Community Support Team

## Re: Average for 3 terms in a row

Hi @Rustami4,

More clear now. What's the "##"? Is the "average for assy" what you want finally?

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Average for 3 terms in a row

no, "##" just means the number it's runnig into the well (1st run, 2nd run)

Yes, exactly. I need the average for Assy. I have a lot of such assy in my table

Community Support Team

## Re: Average for 3 terms in a row

Hi @Rustami4,

I still can't find the "1st run, 2nd run". But maybe you can add it yourself to the formula. You can try it like this:

```Measure =
VAR NumOfSerial =
CALCULATE (
DISTINCTCOUNT ( Table1[Serial#] ),
ALLEXCEPT ( Table1, Table1[Assy#] )
)
VAR Subtotal =
SUMX (
SUMMARIZE (
'Table1',
Table1[Assy#],
Table1[Serial#],
Table1[Repair#],
"sumMeters", SUM ( Table1[Meters] )
),
[sumMeters]
)
RETURN
DIVIDE ( Subtotal, NumOfSerial, 0 )```

Best Regards!

Dale

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

## Re: Average for 3 terms in a row

Hi @Rustami4,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

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