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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
k123
Frequent Visitor

How to calculate a measure based on minimum value of another measure?

Hi, 

 

I would like to write a measure based on my other measure, my data sample is as follows:

 

Score table :

Capture-114.PNG

 

and a factor table:

Capture-115.PNG

 

Both tables are related to a key table:

Capture-116.PNG

I have a measure in my Score table which calculates the minimum across all 'key' , with my 'batch' on a slicer

The measure is a simple min function: MIN(Score[score])

 

I want to calculate the [rate]*[factor] only for minimum scores. Is there a way or formula to do this calculation? Please let me know.

 

 

 

 

 

8 REPLIES 8
Anonymous
Not applicable

@k123 Did it work?

Anonymous
Not applicable

@k123 If I understand correctly, you want to find out min for a batch from first table. For example: For Batch 123, the minimum is 1(b1). Using this minimum for the batch 123(b1). You want to calculate the rate * factor for b1 only for Batch 123.

@Anonymous yes thats right 

Anonymous
Not applicable

Use the below calculation to find the columns which have the min value.

 

IsMinScore = 
VAR MinForBatch = CALCULATE(MIN('Score Table'[Score]),ALLEXCEPT('Score Table','Score Table'[Batch]))

RETURN IF('Score Table'[Score]=MinForBatch,'Score Table'[Score],0)

I am finding the min for every batch using ALLEXCEPT. and then comparing them to the score table. If it is then you can calculate the product, if not then output 0.

@Anonymous thanks for quick response, however I am not able to calculate the product inside the IF statement, since my product consists of two columns: 'factor' and 'rate', I want to multiply both these columns based on my minimum score

Anonymous
Not applicable

@k123 when you say based on your minimum score, what do you mean?

@Anonymous , actually my measure MIN, calculates the minimum score across all keys and for the selected batch on the slicer; so once I get the min score for each keys in a batch, I want to multiply the rate corresponding that minimum score with the factor. I hope I was able to clarify. Thanks

@k123,

 

You may use LOOKUPVALUE Function to add a calculated column first.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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