Forum Discussion
Need support with Calculated measures
Hello,
I have a problem that I need help with as I’m unable to find a solution myself.
I currently have a table visualization set up as bellow where I’m summarizing data from multiple query’s.
What I want is to have a Calculated measure that will show the Stock zone for Location "ZZ" / "OO" / "YY" / "XX" for each distinct order line number on each Delivery number.
Transaction Date | Order number | Delivery number | Order line number | Stock Zone | Location | Stock Zone_2 (TryingToAdd) |
2021-03-19 | 3000000015 | 1000000023 | 5 | E | EE | Z |
2021-03-18 | 3000000015 | 1000000023 | 5 | D | DD | Z |
2021-03-17 | 3000000015 | 1000000023 | 5 | Z | ZZ | Z |
2021-03-16 | 3000000015 | 1000000023 | 5 | B | BB | Z |
2021-03-15 | 3000000015 | 1000000023 | 5 | A | AA | Z |
2021-03-11 | 3000000015 | 1000000023 | 7 | E | EE | O |
2021-03-10 | 3000000015 | 1000000023 | 7 | D | DD | O |
2021-03-09 | 3000000015 | 1000000023 | 7 | O | OO | O |
2021-03-08 | 3000000015 | 1000000023 | 7 | B | BB | O |
2021-03-07 | 3000000015 | 1000000023 | 7 | A | AA | O |
2021-02-24 | 3000000006 | 1000000019 | 1 | E | EE | Y |
2021-02-23 | 3000000006 | 1000000019 | 1 | D | DD | Y |
2021-02-22 | 3000000006 | 1000000019 | 1 | Y | YY | Y |
2021-02-21 | 3000000006 | 1000000019 | 1 | B | BB | Y |
2021-02-20 | 3000000006 | 1000000019 | 1 | A | AA | Y |
2021-01-09 | 3000000001 | 1000000003 | 2 | E | EE | X |
2021-01-08 | 3000000001 | 1000000003 | 2 | D | DD | X |
2021-01-07 | 3000000001 | 1000000003 | 2 | X | XX | X |
2021-01-06 | 3000000001 | 1000000003 | 2 | B | BB | X |
2021-01-05 | 3000000001 | 1000000003 | 2 | A | AA | X |
I will later on filter the table to only have distinct rows for delivery number/Order line number where I will keep the oldest date ones.
Transaction Date | Order number | Delivery number | Order line number | Stock Zone | Location | Stock Zone_2 (TryingToAdd) |
2021-03-19 | 3000000015 | 1000000023 | 5 | E | EE | Z |
2021-03-11 | 3000000015 | 1000000023 | 7 | E | EE | O |
2021-02-24 | 3000000006 | 1000000019 | 1 | E | EE | Y |
2021-01-09 | 3000000001 | 1000000003 | 2 | E | EE | X |
Is this something that's solvable using Calculated measures?
I’m unable to use calculated columns unfortunate.
Hope i have been cleared enough with what im trying to have solved here.
Thanks in advance,
Marcus L
Hi MaQaN ,
Use the measure below:
Measure = CALCULATE(MAX('Table'[Stock Zone]),FILTER(ALL('Table'),'Table'[Order line number] = MAX('Table'[Order line number])&&'Table'[Location] IN{ "ZZ", "OO", "YY" ,"XX"}))For more details, please refer to the pbix file.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
3 Replies
Hello m3tr01d,
A new order line number is generated for each distinkt part on the same Delivery number. So the delivery number 1000000023 from the table consist of atleast 5 difrent parts.
Example:
Deliver number 1000000023
Line 1 = Phone
Line 2 = Computer
Line 3 = Headset
...
All these parts will then go on and be delivered to customer that placed the Order number for specified delivery number.
"What is the rule to compute the last column?"
As for the last column showing Z is what im trying to accomplise. It's should show me the specifik Stock Zone that im intrested in on each order line and where it previus have been to.
In my mind im thinking something like:
IF Stock Zone is "AA" then "Z" en return "Z" as long as Delivery number & Order line number is the same.
Hi MaQaN ,
Use the measure below:
Measure = CALCULATE(MAX('Table'[Stock Zone]),FILTER(ALL('Table'),'Table'[Order line number] = MAX('Table'[Order line number])&&'Table'[Location] IN{ "ZZ", "OO", "YY" ,"XX"}))For more details, please refer to the pbix file.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
