Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi everyone, I realize this question has been asked before but I don't believe the previous solutions are applicable in my case.
I'm looking to create an average of another measure, one that I have for accuracy of shipments vs forecasts, which is included in a matrix of multiple products over a fiscal year. It seems like the only way to get an average is with the values in a column. However, due to the way the data sources are structured, there is no way (that I know of) to do this currently or to add a column to my current tables that would work. I can create a new table, but I would like to avoid that if possible. Any ideas? Thanks!
- m
are you able to provide sample data in text form?
Proud to be a Super User!
Here are scaled down examples of the three main sources.
This first one is forecast data. In any given month, there will be a forecast for a series of future months. While I'm tracking changes in the monthly forecast, I'm mostly concerned about the relevant forecast to measure accuracy. That is, i'll look at what the forecast amount was 3 months ago because of lead time to see if that value matches the shipping. In otherwords, I look at the forecast value for July at input date April. I then compare this with the actual shipments in July, which may entail multiple shipments of same product to the same customer as showin in the second table. The third is a date reference table I have. Since there are so many dates, I've had to create one active relationship with date between the shipping and datekey, defined an inactive relationship between the datekey and forecast, and then use the USERELATIONSHIP formula to get Power BI to do what I want.
@msl924 please can you do it in text not pics, otherwise i can't grab the data
thanks
Proud to be a Super User!
Sorry about that! Does this come through as text?
Forecast Data | ||||
Part | Forecast Input Date | Forecast For | Qty | Relevant Forecast |
A | April | April | 50 | |
A | April | May | 60 | |
A | April | June | 70 | |
A | April | July | 60 | 60 |
B | April | April | 100 | |
B | April | May | 200 | |
B | April | June | 200 | |
B | April | July | 200 | 200 |
Shipping Data | |||
Customer | Part | Ship Date | Qty |
1 | A | 7/1/2018 | 50 |
1 | A | 7/12/2018 | 50 |
DateKey | ||
DateKey | Month No | MoYr |
7/1/2018 | 7 | Jul-18 |
7/2/2018 | 7 | Jul-18 |
7/3/2018 | 7 | Jul-18 |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |