Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?!
Solved! Go to 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?
Hi @Rustami4,
Can you please provide a sample in TEXT mode? The .pbix file would be great.
Best Regards!
Dale
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 |
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
That's not actualy clearly in teхt mode. Look at this
Hi @Rustami4,
More clear now. What's the "##"? Is the "average for assy" what you want finally?
Best Regards!
Dale
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 )
Best Regards!
Dale
Dale, I liked the idea you described. But could you please make it more clear...look at the attached picI 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.
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
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.
This is where i'm right now..
it indicates an error "The function SUMMARIZE is expecting a table name as argument number 6"
You missed the column name like "sumMeters". Double quotes are needed. You can paste your formula here. I can correct it.
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
Статистика ретроспективно - 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