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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

Divide Measure by its Max value

I have two tables. They are related to each other on ID.

BLAIRZ68_0-1644441101962.png

 

I want to calculate -

Final Speed which is = Speed Measure/max (Speed Measure)

 

But I am unable to do so. It is not recognising it as a measure. How should I do this? Is there any different way? Pls, let me know. Thanks in Advance!

 

These I calculated-

Measure in Table 2 →Speed Measure = sum('Table2'[Quantity])/Sum('Table1’'[Time])

 

Calculated Column in Table 1→ Time = (DATEDIFF('Table1’[Start Time], 'Table2’'[End Time],SECOND))

 

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

How about this:

tomfox_0-1644517093183.png

ID 11 has the maximum speed, which is why the measure shows 1.

 

This is the measure I used:

Speed 1 mes = 
VAR _helpTable  = 
            SUMMARIZE (
                ALLSELECTED ( Table2 ),
                [ID],
                "speed", [speed1]
            )
VAR _maxSpeed = CALCULATE ( MAXX ( _helpTable , [speed] ) )
RETURN
 DIVIDE ( Table2[Speed1], _maxSpeed )

 

Hope this is it!

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

 

 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

@tackytechtomThanks for helping. It is giving 1 as an answer for all rows. It is not giving correct output.

I am not sure what the ID in your model does, so it might work if you remove the 'Table'[ID] in the SUMMARIZE function. Otherwise, you are welcome to share some data and show a possible outcome of what you would like to achieve 🙂

 

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Anonymous
Not applicable

BLAIRZ68_0-1644488415868.png

@tackytechtom I am getting 1 as an answer for all values.

 

Anonymous
Not applicable

@tackytechtom Thanks for answering. I am sharing the details.

 

Time = (DATEDIFF('Table1'[start time], 'Table1'[end time],SECOND))
Speed1 = SUM(Table2[Volume])/sum(Table1[Time])
Speed 1 col = DIVIDE(Table2[Volume], SUM(Table1[Time]))
These are two tables linked with each other on ID.These are two tables linked with each other on ID.If I divide columns as measure, it gives correct answer. But when I divide it as calculated column, answer is not correct.If I divide columns as measure, it gives correct answer. But when I divide it as calculated column, answer is not correct.
 
I just wanted to divide the speed with max speed. Thanks in advance!

Hi @Anonymous ,

 

How about this:

tomfox_0-1644517093183.png

ID 11 has the maximum speed, which is why the measure shows 1.

 

This is the measure I used:

Speed 1 mes = 
VAR _helpTable  = 
            SUMMARIZE (
                ALLSELECTED ( Table2 ),
                [ID],
                "speed", [speed1]
            )
VAR _maxSpeed = CALCULATE ( MAXX ( _helpTable , [speed] ) )
RETURN
 DIVIDE ( Table2[Speed1], _maxSpeed )

 

Hope this is it!

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

 

 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Anonymous
Not applicable

@tackytechtom Thanks for the solution and your help. It worked. Thank you so much!

Anonymous
Not applicable

BLAIRZ68_0-1644517810495.png

@tackytechtom Hey I use the formula but it is still giving 1. Not sure, what I am doing wrong here.

 

Hi @Anonymous ,

It seems like you are using the ID column from table1. Try using it from table2 instead.

 

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

tackytechtom
Super User
Super User

Hi @Anonymous ,

 

I am not entirely sure whether this one here works for you, but it might be worth a try 🙂 

Measure = 
VAR _helpTable  = SUMMARIZE ( 'Table', 'Table'[ID], "speed", Table[Speed Measure] )
VAR _maxSpeed = MAXX ( _helpTable , [speed] ) 
RETURN
DIVIDE ( CALCULATE(Table[Table[Speed Measure]] ), _maxSpeed )

 

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.