cancel
Showing results for
Did you mean:

## Multiplying measure results from selected month to all months from a different column

Hey everyone,

I’ve got a simple concept I’m struggling with calculating.

I want to display the \$/UOM at a point in time the user selects through the calendar year and apply that rate to the forecast qty

Here’s what I have:

• A table used as a slicer for the user to display which month’s forecast they want to use (Jan forecast, Feb forecast, etc.)
• A table of total \$ and \$/UOM actuals by month
• A table of forecasted qty by both month and month version (i.e. Jan version has Jan-Dec forecast qty, Feb version has Feb-Dec forecast qty, etc)
• A generic calendar table – connected to both the actuals and forecast table

My end goal is to display a bar chart that shows actual \$ and forecast \$ by month based on which forecast version the user selects (i.e. If user selects Feb forecast, it will show Jan actual \$ + Feb thru Dec forecast qty * Jan actual \$/IEA to get forecasted \$ for Feb thru Dec

Here’s what I’ve got:

• Total Actuals \$ and \$/UOM by Month – complete
• Calculated Actual \$ - Measure that only displays the actuals based on the monthly forecast version (in the case below the forecast version selected was May, so actuals were displayed only for Jan – Apr) – complete
• Measure that grabs the \$/UOM of the last month of actuals based on the monthly forecast version selected (since May was selected – April’s \$/UOM of \$35.20 is displayed) – complete
• Forecast Qty for future months based on forecast selected (since May was selected – May thru December qty is displayed – in this specific case there was no forecast volume for May which is why it’s blank) – complete

What I’m missing

• Measure that takes the Selected \$/UOM (in this case of \$35.20) and multiply that by each of the Calculated Forecast Qty values to get the Forecast \$ (it calculates at the total level - \$35.20 * 6,854 = \$241,263.54, but I need it at the month level so I can chart it)

Image of Table

1 ACCEPTED SOLUTION

Firstly, congratulations on laying out your measures in a table so you can see what’s is happening. This is essential for more complex DAX. You can see that your uom measure is only being returned for month 4 and the total. You need the number to be returned for all months. Stated differently, you need to remove the filter from what ever is causing the measure to only work for month 4. It is probably something like CALCULATE(your current code, all(calendar[month number]))

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
2 REPLIES 2

Firstly, congratulations on laying out your measures in a table so you can see what’s is happening. This is essential for more complex DAX. You can see that your uom measure is only being returned for month 4 and the total. You need the number to be returned for all months. Stated differently, you need to remove the filter from what ever is causing the measure to only work for month 4. It is probably something like CALCULATE(your current code, all(calendar[month number]))

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

I knew it had to be something simple.  Now I can I enjoy the rest of my weekend.  Thanks

Announcements

#### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors