Using Results of a Measure as Input for Another Measure/Column

[jiwanicki]

Hello,

I currently have a measure to calculate the amount of material dispatched from certain locations, along with their destinations. Each origin dispatches to multiple destinations, with some destinations receiving materials from different points of origin. Most of the points of origin are also destinations due to the nature of shipping/receiving material.

What I would like to achieve is determine how much material each origin point received, using the results of the existing measure calculating how much each point of origin dispatched. I am not sure this is possible via measure, but I am looking for assistance on how to best make this happen within PBI.

Thank you in advance,

Jacek

[HiteshDataXpert · Accepted solution]

To achieve your goal of determining how much material each origin point received using the existing measure that calculates how much each point of origin dispatched, you can leverage DAX (Data Analysis Expressions) in Power BI. Here's a step-by-step approach to accomplish this:

- Ensure you have a table that tracks dispatches, including columns for `Origin`, `Destination`, and `DispatchedAmount`.
- If your `Origin` and `Destination` are part of the same list of locations, you might have a single `Locations` table that is related to your dispatches table.

 2. **Create a Measure for Dispatched Material**
Assuming you already have a measure for dispatched material, let's call it `TotalDispatched`.

DAX:
TotalDispatched = SUM(Dispatches[DispatchedAmount])

3. **Create a Measure for Received Material**
To calculate the amount of material received by each origin point, you need to sum the `DispatchedAmount` where the `Destination` is the same as the `Origin`.

DAX
TotalReceived =
CALCULATE(
[TotalDispatched],
FILTER(
Dispatches,
Dispatches[Destination] = Dispatches[Origin]
)
)

This measure filters the `Dispatches` table to only include rows where the `Destination` is the same as the `Origin`, effectively giving you the amount of material received by each origin point.

4. **Use the Measures in Visuals**
- You can now use the `TotalReceived` measure in your visuals (e.g., tables, matrices, or charts) to display the amount of material received by each origin point.
- If you want to see both dispatched and received amounts side by side, you can create a table visual with `Origin` as the rows and `TotalDispatched` and `TotalReceived` as the values.

 5. **Handle Multiple Origins and Destinations**
If your data model is more complex, with multiple origins and destinations, you might need to adjust the measure to account for these relationships. For example, if you have a separate `Locations` table, you might need to use `RELATED` or `RELATEDTABLE` functions to correctly calculate the received amounts.

DAX
TotalReceived =
CALCULATE(
[TotalDispatched],
FILTER(
Dispatches,
Dispatches[Destination] = Locations[LocationID]
)
)

In this case, `Locations[LocationID]` would be the key that links the `Dispatches` table to the `Locations` table.

[lbendlin]

You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.