Skip to main content
cancel
Showing results for 
Search instead 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

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

 

Table.PNG

 

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.

 

Table 2.PNG

 

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

 

Thanks!

2 ACCEPTED SOLUTIONS
OwenAuger
Super User
Super User

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:image.png

 

 

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
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

v-juanli-msft
Community Support
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]
 
 1.png
 
 
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
 
2.png
 
Second, from this original table, create a new table
Table = FILTER(Sheet1,[maxvalue]=[Connection Time])
 
3.png
 
Third, I would summarize from the second table
Table 2 = SUMMARIZE('Table',[Rig],[Driller],[Time from best1])
 
4.png
 
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

View solution in original post

4 REPLIES 4
v-juanli-msft
Community Support
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]
 
 1.png
 
 
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
 
2.png
 
Second, from this original table, create a new table
Table = FILTER(Sheet1,[maxvalue]=[Connection Time])
 
3.png
 
Third, I would summarize from the second table
Table 2 = SUMMARIZE('Table',[Rig],[Driller],[Time from best1])
 
4.png
 
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

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

OwenAuger
Super User
Super User

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:image.png

 

 

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
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors