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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
AndreGiovannini
Frequent Visitor

Measure to return a field based on two measures comparison

Hi,

 

I have a measure named 'Total Cost' that is based on (SUM('Table1'[Qty]) * SUM('Table2'[Cost]).
This 'Total Cost' is break by Item, Origin and Destination in a table resulting in:

 

Item | Origin | Destination | Total Cost
123 | BRA | BEL | 100
123 | ARG | BEL | 120

312 | BRA | COL | 300
(...)

I've created a 'Min Total Cost' measure to point out the best source cost:

CALCULATE(
MINX('Table2',[Total Cost]),
ALLSELECTED('Table2[Origin],'Table2'[Item-Origin])
)

This measure is working fine, so added to above:
Item | Origin | Destination | Total Cost | Min Cost
123 | BRA | BEL | 100 | 100
123 | ARG | BEL | 120 | 100

Now I want to point out what is the best source in each of the lines.
Item | Origin | Destination | Total Cost | Min Cost | Best Origin
123 | BRA | BEL | 100 | 100 | BRA
123 | ARG | BEL | 120 | 100 | BRA

How can I create the measure to point the 'Best Origin'?

1 ACCEPTED SOLUTION
eliasayyy
Memorable Member
Memorable Member

hello please follow

first you can shorten your measure for minx with 

Lowest price = 
MINX(
    ALLSELECTED('Table'[Origin]),
    [Sum cost]
)

 

second you need to use topn

origin best = 
TOPN(
    1,ALLSELECTED('Table'[Origin]),[Sum cost],ASC)

annonymous1999_0-1683295226095.png

 



View solution in original post

6 REPLIES 6
eliasayyy
Memorable Member
Memorable Member

hello please follow

first you can shorten your measure for minx with 

Lowest price = 
MINX(
    ALLSELECTED('Table'[Origin]),
    [Sum cost]
)

 

second you need to use topn

origin best = 
TOPN(
    1,ALLSELECTED('Table'[Origin]),[Sum cost],ASC)

annonymous1999_0-1683295226095.png

 



Hi @eliasayyy ,

 

The image you sent is exactly what I need, but I could not replicate it in my model.

There is no error flag on measure, but once add it to a visual the following msg appears:

"MdxScript(Model) (75,1) Calculation error in measure 'SUP Measures'[Best Source]: A table of multiple values was supplied where a singular value was expected."

I believe that this is related to fields I'm aggregating in ALLSELECTED. Tried some other selections but couldnt solve it.

Any thougts of what I'm missing?

Can you send me the measure you used

Sure, here is it:

Best Source =
TOPN(1,
ALLSELECTED('DIM Avail Org'[Avail Org Code])
,[Total Landed]
,ASC)

I've been playing around with fields on ALLSELECTED, but couldnt solve it yet.

EDIT:
There is a Joiner table affecting this measure. Added the 'ALLEXCEPT' and measure do no break anymore. However I could not identify which exceptions should I use yet.

Best Source =
CALCULATE(
TOPN(1,
ALLSELECTED(JNR[Avail Org])
,[Total Landed]
,ASC)
,ALLEXCEPT(JNR,JNR[Avail Org]
))

Is there a relationship between table 1 and table 2?

Hi @eliasayyy 

Flagging your solution as accepted. Was able to replicated it in a different data model.

Thank you.

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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