cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rustami4 Regular Visitor
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
Rustami4 Regular Visitor
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
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.
Rustami4 Regular Visitor
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 Man Wink 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,mmBit TypeRepair#Assy#Serial#Р IH dateР РћРћH dateInterval,inInterval outMetersRРћР ,m/h
393,7R519SDHXUnewA05958LC900085202.09.201602.09.201610504095,23809524
295,3R519SDHXUR1A20189900045902.09.201603.09.20165098393360,86105675
215,9VS516DG1HXUnewA20783900111605.09.201608.09.20169832485150249,50560316
215,9VM513DGHXnewA20062900106809.09.201611.09.20162485315266728,370906
393,7R519SDHXUnewA05958LC601391331.05.201602.06.20166752245544,3902439
295,3R619SDHXUR1A05914900077803.06.201612.06.2016522133981714,59189141
220,7VM416DGHXnewA04892600845114.06.201621.06.201613392596125734,83924612
220,7HE14MRSVroller cone8792138304808.01.201609.01.2016959960112,5
220,7V613DGHXR1A20061900067709.01.201621.01.20169602809184923,86114337
220,7V613DGHXR1A20061900067721.01.201626.01.20162809321040116,42096642
220,7V613DGHXR1A20070900082431.01.201606.02.20162836317634012,71503366
142,9V613DG1XR1A05894900034016.02.201622.02.20163176372054418,71345029
393,7R519SDHXUnewA05958LC900085217.08.201617.08.201610504053,33333333
295,3R519SDHXUR1A20189900045917.08.201619.08.201650100295255,73770492
215,9VS516DG1HXUnewA20783900120923.08.201628.08.201610023199219751,69411765
393,7R519SDHXUnewA05958LC900085216.07.201616.07.2016104737123,3333333
295,3R519SDHXUR1A20189900045916.07.201618.07.201647102798061,51914626
155,6VS416DGHnewA20768900109602.08.201606.08.20163395404064530,14018692
393,7R519SDHXUnewA05958LC900085225.04.201625.04.201612483662,06896552
295,3VM516DG1HXR1A20296900074925.04.201628.04.20164897893055,12744517
215,9V613SG1XnewA20337LC900077402.05.201606.05.20169783007202943,91774892
215,9V613DG1HXnewA20337LC900071811.05.201612.05.2016303731329541,30434783
490L111Groller coneR019458149761525.06.201626.06.20160616122,18181818
393,7R616SDGHXUR1A05955601459526.06.201629.06.20166150844737,25
295,3R619SDHXUR1A05914900077801.07.201606.07.2016508127576725,14754098
220,7VM416DGHXR2A04892600845108.07.201616.07.201612752601132625,5
393,7R519SDHXUnewA05958LC900085203.04.201603.04.201612463468
215,9V613SG1XnewA20337LC900077409.04.201615.04.20169893169218045,27518172
393,7R519SDHXUnewA05958LC900085218.06.201618.06.201610504080
295,3R519SDHXUR1A20189900045918.06.201620.06.201650103398365,18567639
155,6VM416DGnewA04920600899204.07.201612.07.20163436441898218,88098443
393,7HR1GJMRSroller cone8808138240426.05.201628.05.20160656513
393,7VTD519SDHXUR1A05216601020728.05.201603.06.20166555549028,67173786
295,3R519SDHXR5A05962LC601532713.06.201626.06.2016555106751226,47362978
295,3R519SDHXR5A05962LC601532726.06.201629.06.20161067139833123,22807018
295,3R519SDHXR5A05962LC601532729.06.201602.07.20161398165325526,15384615
215,9V613SG1XnewA20337LC900087413.07.201621.07.20161654233267813,51405222
215,9V613SG1XnewA20337LC900087422.07.201623.07.201623322346149,85915493
215,9V613SG1XnewA20337LC900087425.07.201626.07.201623682382148
215,9V613SG1XnewA20337LC900087429.07.201630.07.201624422453114,247104247
215,9V613SG1XnewA20337LC900087403.08.201603.08.2016253825501216
215,9V613SG1XnewA20337LC900087404.08.201604.08.2016255025904010
215,9V613SG1XnewA20337LC900087405.08.201606.08.2016259026152520
215,9V613SG1XnewA20337LC900087408.08.201609.08.201626392669304,231311707
215,9V613SG1XnewA20337LC900087410.08.201611.08.201626742706328,533333333
215,9V613SG1XnewA20337LC900087412.08.201613.08.201627242770464,679552391
Community Support Team
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] )

Average for 3 terms in a row.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.
Rustami4 Regular Visitor
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

ASSYSerials##TotalNEWR1R2R3
Рђ0489260084511st run 1000800600400
60084512nd run 1100900700600
 Sum for serial61002100170013001000
90000201st run 2000180015001200
90000202nd run 140012001000200
 Sum for serial103003400300025001400
90000101st run 2300200016001200
90000102nd run 1800160014001200
 Sum for serial131004100360030002400
        
        
   AVERAGE FOR ASSY3200,002766,672266,671600,00
Rustami4 Regular Visitor
Regular Visitor

Re: Average for 3 terms in a row

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

Снимок.PNG

Community Support Team
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.
Rustami4 Regular Visitor
Regular Visitor

Re: Average for 3 terms in a row

Hi, @v-jiascu-msft

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
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 )

Average for 3 terms in a row.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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
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.