cancel
Showing results 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

New Member

## Summary of Similar Rows Used in Calculation

I have a large table that is similar to the one below. My desired visual will have the difference between the best and worst driller's connections times on each rig. I've attempted to use the Summarize function and creating new tables and relationships in my data model, but I can't seem to put it together to get what I'm looking for.

Desired Visual will be a table that will look like this, but with the final number instead of the equation, but I put the equation in to hopefully be more clear about what I'm trying to make happen.

Also, would it be any different if the initial table in this was already summarized from another table?

Thanks!

2 ACCEPTED SOLUTIONS
Super User

My approach would be to keep the table you have in your post, and create some measures. I played around with your table and created these measures (I called the table DrillTime):

```Best Time =
MIN ( DrillTime[Connection Time] )

Worst Time =
MAX ( DrillTime[Connection Time] )

Time from Best =
[Worst Time] - [Best Time]

Worst Driller =
CONCATENATEX (
TOPN (
1,
VALUES ( DrillTime[Driller] ),
CALCULATE ( AVERAGE ( DrillTime[Connection Time] ) ), DESC
),
DrillTime[Driller],
", ",
DrillTime[Driller]
)```

If I put these in a table with Rig I get:

Note that Worst Driller concatenates ties.

Are you able to get these working? I can post a link to my model later today if useful.

Regards,

Owen 🙂

Owen Auger
Blog
Community Support
Hi
I test with a simpler method  by creating measures.

max = CALCULATE(MAX(Sheet1[Connection Time]),ALLEXCEPT(Sheet1,Sheet1[Rig]))
min = CALCULATE(MIN(Sheet1[Connection Time]),ALLEXCEPT(Sheet1,Sheet1[Rig]))
Worst driller = CALCULATE(MAX([Driller]),FILTER(ALLEXCEPT(Sheet1,Sheet1[Rig]),[Connection Time]=[max]))
Time from best2 = [max]-[min]

Also I have a trial by summarizing tables.
If the initial table was already summarized from another table, it would make no difference to summarize a new table from it.
Let me show an example according to your requirement.
First, I create two calculated columns in the original table.

maxvalue = CALCULATE(MAX([Connection Time]),ALLEXCEPT(Sheet1,Sheet1[Rig]))

Time from best1 = var M=CALCULATE(MAX([Connection Time]),ALLEXCEPT(Sheet1,Sheet1[Rig])) var L=CALCULATE(MIN([Connection Time]),ALLEXCEPT(Sheet1,Sheet1[Rig])) return M-L

Second, from this original table, create a new table
Table = FILTER(Sheet1,[maxvalue]=[Connection Time])

Third, I would summarize from the second table
Table 2 = SUMMARIZE('Table',[Rig],[Driller],[Time from best1])

Let me draw attention to the fourth and fifth row, it shows duplicate values for “Rig” and “Time from best1”, because there are same Connection Time for different Drillers in the original table.

Best Regards
Maggie
4 REPLIES 4
Community Support
Hi
I test with a simpler method  by creating measures.

max = CALCULATE(MAX(Sheet1[Connection Time]),ALLEXCEPT(Sheet1,Sheet1[Rig]))
min = CALCULATE(MIN(Sheet1[Connection Time]),ALLEXCEPT(Sheet1,Sheet1[Rig]))
Worst driller = CALCULATE(MAX([Driller]),FILTER(ALLEXCEPT(Sheet1,Sheet1[Rig]),[Connection Time]=[max]))
Time from best2 = [max]-[min]

Also I have a trial by summarizing tables.
If the initial table was already summarized from another table, it would make no difference to summarize a new table from it.
Let me show an example according to your requirement.
First, I create two calculated columns in the original table.

maxvalue = CALCULATE(MAX([Connection Time]),ALLEXCEPT(Sheet1,Sheet1[Rig]))

Time from best1 = var M=CALCULATE(MAX([Connection Time]),ALLEXCEPT(Sheet1,Sheet1[Rig])) var L=CALCULATE(MIN([Connection Time]),ALLEXCEPT(Sheet1,Sheet1[Rig])) return M-L

Second, from this original table, create a new table
Table = FILTER(Sheet1,[maxvalue]=[Connection Time])

Third, I would summarize from the second table
Table 2 = SUMMARIZE('Table',[Rig],[Driller],[Time from best1])

Let me draw attention to the fourth and fifth row, it shows duplicate values for “Rig” and “Time from best1”, because there are same Connection Time for different Drillers in the original table.

Best Regards
Maggie
New Member

Thanks, I wound up using your expression to get the names!

Super User

My approach would be to keep the table you have in your post, and create some measures. I played around with your table and created these measures (I called the table DrillTime):

```Best Time =
MIN ( DrillTime[Connection Time] )

Worst Time =
MAX ( DrillTime[Connection Time] )

Time from Best =
[Worst Time] - [Best Time]

Worst Driller =
CONCATENATEX (
TOPN (
1,
VALUES ( DrillTime[Driller] ),
CALCULATE ( AVERAGE ( DrillTime[Connection Time] ) ), DESC
),
DrillTime[Driller],
", ",
DrillTime[Driller]
)```

If I put these in a table with Rig I get:

Note that Worst Driller concatenates ties.

Are you able to get these working? I can post a link to my model later today if useful.

Regards,

Owen 🙂

Owen Auger
Blog
New Member

Thanks! I've got my table up and running!