Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
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
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
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!!!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |