Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin 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.
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'?
Solved! Go to Solution.
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)
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)
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.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
15 | |
9 | |
7 | |
7 | |
6 |
User | Count |
---|---|
22 | |
11 | |
10 | |
10 | |
8 |