Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rustami4
Helper II
Helper II

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

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? 

View solution in original post

27 REPLIES 27
v-jiascu-msft
Employee
Employee

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.

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

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.

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

Снимок.PNG

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.

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

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.

Dale, I liked the idea you described. But could you please make it more clear...look at the attached picСнимок.PNGI can't get what the encircled values means?! That "sumMeters", i'm typing this and my PBI indicates that's wrong

Hi @Rustami4,

 

The encircled value is a parameter of Summarize. So you can't use it somewhere else unless you create a table with Summarize.

Average for 3 terms in a row.jpg 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The second encircled value is a parameter of SUMX, which is inherited from Summarize. How did you use it in your formula?

 

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.

So...the first encircled value is a parameter of sum for 3 distinct parameters (serial, assy,repair), right? I'm just wondering how did you specify the task for it technicially? how this measure function looks

i mean what this column calculates

I'm currently on my way to get this "sumMeters" 🙂 I understand the whole idea, left details

 Column Name   Column Content
"sumMeters", SUM ( Table1[Meters] )

These are one pair of parameters.  Column name and its content. Please refer to Summarize.

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.

This is where i'm right now..

it indicates an error "The function SUMMARIZE is expecting a table name as argument number 6"

 

Screenshot.PNG

 

 

You missed the column name like "sumMeters". Double quotes are needed. You can paste your formula here. I can correct it.

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.

Here is the formula:  VAR Subtotal=SUMX(Summarize('Table name'; 'Table name'[ASSY#]; 'Table name'[Seria#];'Table name'[Repair#];'Table name'[Meters]; SUM('Table name'[Meters])); 'Table name'[Meters]))

Hi @Rustami4,

 

It could be this one:

VAR Subtotal =
SUMX (
    SUMMARIZE (
        'Table name';
        'Table name'[ASSY#];
        'Table name'[Seria#];
        'Table name'[Repair#];
        'Table name'[Meters];
        "SumMeters"; SUM ( 'Table name'[Meters] )
    );
    [SumMeters]
)

 

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.

Статистика ретроспективно - is a table name

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? 

Hi @Rustami4,

 

The encircled value is just a column name of the virtual table returned by Summarize. Which step are you in?

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.