Summary of Similar Rows Used in Calculation

[justclickinbtns]

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!

[OwenAuger · Accepted solution]

Hi @justclickinbtns

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 🙂

[v-juanli-msft · Accepted solution]

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

[justclickinbtns]

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

[OwenAuger · Accepted solution]

Hi @justclickinbtns

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 🙂

[justclickinbtns]

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