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.
I have two tables; one contains the forecast values and the other contains values that increases with time (monthly). I want to subtract the maximum value for each record from the forecast values. How do I do this in DAX? The table is linked by the location number (unique ID). The forecast values are constant for each location.
Sample data below for the forecast and varing data.
Location number | Forecast value |
01 | 500 |
02 | 325 |
03 | 460 |
04 | 300 |
05 | 250 |
Location number | Date | Value |
01 | Jan 2022 | 65 |
01 | Feb 2022 | 100 |
02 | Apr 2022 | 20 |
03 | May 2022 | 100 |
04 | Dec 2021 | 50 |
Example the difference for location 01 in Jan 2022 is 500 - 65 = 435. In Feb 2022 it's 400. I want this for all location numbers. I need this to information in my report to know how much of the forecast is remaining.
Also, how do I sort my legend in ascending order? See below
Any help will be greatly appreciated.
Solved! Go to Solution.
Hi @Anonymous
You can try this measure,
diff =
var _forecast= MAXX(FILTER(forecast,forecast[Location number]=MAX('Table'[Location number])),[Forecast value])
return _forecast-MAX('Table'[Value])
if you need calculated column, try this,
Column =
var _forecast= MAXX(FILTER(forecast,forecast[Location number]=EARLIER('Table'[Location number])),[Forecast value])
return _forecast-'Table'[Value]
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
You can try this measure,
diff =
var _forecast= MAXX(FILTER(forecast,forecast[Location number]=MAX('Table'[Location number])),[Forecast value])
return _forecast-MAX('Table'[Value])
if you need calculated column, try this,
Column =
var _forecast= MAXX(FILTER(forecast,forecast[Location number]=EARLIER('Table'[Location number])),[Forecast value])
return _forecast-'Table'[Value]
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
@v-xiaotang thanks for the response.
This method works however I have a little issue. Some of the location number have alphanumeric IDs. Is there a way to go around this?
Hi @Anonymous
If you use my measure, please make sure that the columns in the blue box are of the same type, either numeric or textual
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
I got an error when I tried the formula.
@Anonymous , Make sure both tables are joined with the common location table
Sum(Table[Forecast Value]) - Sum(Table2[Value])
Should work
or
sumx(Values('Date'[Month Year]), calculate(Sum(Table[Forecast Value]) - Sum(Table2[Value]) ) )
best it to have crossjoin with distinct monthstart date and join with date table
new Table= crossjoin([Table], distinct('Date'[Month Start Date]) )
again the first measure will work
@amitchandak thanks for the response.
I tried both methods but I got the same difference value for each location number which is not what I want.
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 |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |