cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
luketerry
Advocate II
Advocate II

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)

Thanks for your help

 

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.

View solution in original post

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.

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

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.

secondImage

Experience what’s next for Power BI

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