Forum Discussion
Advocate IIFilter that will Select the Closest Related Predicted Value
Hi Guys,
I've Created a Graph in power bi that has the values on the y-axis, and the month number on the x-axis, something that looks similar to the below sample chart.
Basically, what I need to do, is to find the "Prediction Grade" that most closely resembles the "Actual Value", and have the visual select that Predicted Grade, when I select a Name.
For example, when I Select "Name 111", the visual will automatically Select "Prediction Grade B", since the average values throughout their tables is the most similar.
(Average of Months 1-12 of "Prediction Grade B" is 19, and Average of Months 1-6 of "Name 111" is 20.8 - therefore those are the best matches..
Average of Months 1-12 of "Prediction Grade A" is 11.7, and Average of Months 1-6 of "Name 222" is 10.33 - therefore those are the best matches )
A sample of my two tables is below.
Predicted Table
| Prediction Grade | Month | Predicted Value |
| A | 1 | 8 |
| A | 2 | 9 |
| A | 3 | 15 |
| A | 4 | 13 |
| A | 5 | 9 |
| A | 6 | 16 |
| A | 7 | 13 |
| A | 8 | 8 |
| A | 9 | 14 |
| A | 10 | 12 |
| A | 11 | 8 |
| A | 12 | 15 |
| B | 1 | 21 |
| B | 2 | 28 |
| B | 3 | 13 |
| B | 4 | 10 |
| B | 5 | 20 |
| B | 6 | 30 |
| B | 7 | 28 |
| B | 8 | 24 |
| B | 9 | 20 |
| B | 10 | 13 |
| B | 11 | 16 |
| B | 12 | 5 |
Actual Table
| Name | Month | Actual Value |
| 111 | 1 | 22 |
| 111 | 2 | 29 |
| 111 | 3 | 15 |
| 111 | 4 | 5 |
| 111 | 5 | 20 |
| 111 | 6 | 34 |
| 222 | 1 | 10 |
| 222 | 2 | 9 |
| 222 | 3 | 11 |
| 222 | 4 | 12 |
| 222 | 5 | 15 |
| 222 | 6 | 5 |
Hi aar0n,
Please try these measures:
Average for actual = CALCULATE ( AVERAGE ( Actual[Actual Value] ), ALLEXCEPT ( Actual, Actual[Name] ) ) Average for predicted = CALCULATE ( AVERAGE ( Predicted[Predicted Value] ), ALLEXCEPT ( Predicted, Predicted[Prediction Grade] ) ) diff = ABS([Average for actual]-[Average for predicted]) Rank = CALCULATE ( RANKX ( ALL ( Predicted ), [diff],, ASC, DENSE ), ALLEXCEPT ( Predicted, Predicted[Prediction Grade] ) )To create the line chart, you should add both [Month] field and [Prediction Grade] into X-Axis. And add [Rank] measure into visual level filter, set its value to 1.
I have uploaded the .pbix file for your reference.
Best regards,
Yuliana Gu
2 Replies
v-yulgu-msftMicrosoft Employee
Hi aar0n,
Please try these measures:
Average for actual = CALCULATE ( AVERAGE ( Actual[Actual Value] ), ALLEXCEPT ( Actual, Actual[Name] ) ) Average for predicted = CALCULATE ( AVERAGE ( Predicted[Predicted Value] ), ALLEXCEPT ( Predicted, Predicted[Prediction Grade] ) ) diff = ABS([Average for actual]-[Average for predicted]) Rank = CALCULATE ( RANKX ( ALL ( Predicted ), [diff],, ASC, DENSE ), ALLEXCEPT ( Predicted, Predicted[Prediction Grade] ) )To create the line chart, you should add both [Month] field and [Prediction Grade] into X-Axis. And add [Rank] measure into visual level filter, set its value to 1.
I have uploaded the .pbix file for your reference.
Best regards,
Yuliana Gu
- aar0n
It Seems to work great on the sample file, but i'm having issues in my file where as soon as i filter it to "1", the graph disappears.
i will just have to play around in the next day or two and see what could be causing that.
thank you very much for the help!!!