Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Superheroes!
I am having an issue with my Lookup-function returning a table instead of a single value.
I have two relevant tables, plus date.
The Lookup function below is meant to return the price for a product given to a customer for the year in question, and multiply with the number if units.
However, there appears to be duplicates in the Account Overview (excel)table, i.e. multiple prices for the same product for the same customer withing the same year, and I would like to bypass this by having the below formula only return the highest price found. In short, if the formula returns more than one price for the same product to the same customer within the same year, I would only want the highest one.
Solved! Go to Solution.
Hi @Anonymous
Revenue =
SUMX (
f_Forecast,
f_Forecast[Units]
* CALCULATE (
MAX ( 'dAccount Overview'[Price per unit] ),
FILTER (
ALL (
'dAccount Overview'[SKU],
'dAccount Overview'[Number],
'dAccount Overview'[Year]
),
'dAccount Overview'[SKU] = f_Forecast[SKU]
&& 'dAccount Overview'[Number] = f_Forecast[Number]
&& 'dAccount Overview'[Year] = YEAR ( f_Forecast[Forecast Shipping Date] )
)
)
)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @Anonymous
Revenue =
SUMX (
f_Forecast,
f_Forecast[Units]
* CALCULATE (
MAX ( 'dAccount Overview'[Price per unit] ),
FILTER (
ALL (
'dAccount Overview'[SKU],
'dAccount Overview'[Number],
'dAccount Overview'[Year]
),
'dAccount Overview'[SKU] = f_Forecast[SKU]
&& 'dAccount Overview'[Number] = f_Forecast[Number]
&& 'dAccount Overview'[Year] = YEAR ( f_Forecast[Forecast Shipping Date] )
)
)
)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |