Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am creating measures that are averages based on specific columns.
eg.
Avg1 = AVERAGE('Table'[Rate])
**BTW Not sure if this is the best way to implement my averages as opposed to using something else like
(
Avg1 = CALCULATE(AVERAGE('Table'[Rate))
vs
)
1. I created my multiple Avg measures.
2. I created a Rank based off each of those measures
Below is what I see when I insert a matrix with the avgs and ranks (just 3 of the ranks shown for simplicty)
Based on the averages, the slicer applied and all items shown. THIS IS ALL CORRECT (except the Rank2 and Rank3 are starting at #2 instead of #1. Just another thing I'm trying to figure out...)
Items | Avg1 | Rank1 | Avg2 | Rank2 | Avg3 | Rank3 | Avg4 | Rank4 | Avg5 | Rank5 | Avg6 | Rank6 | Overall Rank |
Item1 | 9.14 | 10 | 6.96 | 7 | 2.96 | 2 | 77.90 | 1 | 76.64 | 1 | 7.56 | 2 | 1 |
Item2 | 6.87 | 17 | 8.67 | 16 | 4.92 | 15 | 44.79 | 17 | 51.97 | 16 | 7.86 | 3 | 15 |
Item3 | 10.86 | 3 | 7.17 | 10 | 4.61 | 11 | 54.35 | 12 | 58.19 | 9 | 7.92 | 4 | 7 |
Item4 | 9.06 | 11 | 6.99 | 9 | 4.20 | 9 | 68.48 | 2 | 63.99 | 3 | 8.44 | 5 | 5 |
Item5 | 8.00 | 16 | 4.77 | 2 | 11.89 | 17 | 45.88 | 15 | 54.02 | 14 | 8.58 | 6 | 12 |
Item6 | 10.89 | 1 | 6.57 | 6 | 4.03 | 6 | 57.61 | 7 | 68.98 | 2 | 8.97 | 7 | 2 |
Item7 | 10.32 | 4 | 5.81 | 4 | 4.69 | 12 | 54.37 | 11 | 52.29 | 15 | 8.99 | 8 | 8 |
Item8 | 9.85 | 6 | 7.84 | 13 | 4.18 | 8 | 53.92 | 13 | 54.66 | 13 | 9.03 | 9 | 10 |
Item9 | 9.89 | 5 | 7.33 | 11 | 5.07 | 16 | 56.13 | 9 | 55.88 | 12 | 9.18 | 10 | 11 |
Item10 | 8.78 | 14 | 6.36 | 5 | 4.83 | 13 | 60.40 | 4 | 57.82 | 11 | 9.52 | 11 | 9 |
Item11 | 9.59 | 7 | 5.54 | 3 | 3.91 | 3 | 66.66 | 3 | 62.27 | 5 | 9.59 | 12 | 3 |
Item12 | 8.19 | 15 | 7.62 | 12 | 4.54 | 10 | 59.32 | 5 | 60.43 | 7 | 9.69 | 13 | 10 |
Item13 | 10.87 | 2 | 6.97 | 8 | 4.02 | 5 | 57.55 | 8 | 61.30 | 6 | 10.23 | 14 | 6 |
Item14 | 9.47 | 8 | 8.49 | 15 | 3.94 | 4 | 58.38 | 6 | 58.12 | 10 | 10.54 | 15 | 9 |
Item15 | 8.98 | 12 | 8.06 | 14 | 4.14 | 7 | 56.05 | 10 | 63.20 | 4 | 10.85 | 16 | 11 |
Item16 | 8.89 | 13 | 8.89 | 17 | 4.88 | 14 | 53.29 | 14 | 59.23 | 8 | 11.66 | 17 | 14 |
Item17 | 9.24 | 9 | 10.00 | 18 | 45.64 | 16 | 45.68 | 17 | 12.33 | 18 | 13 |
Result when a specific Item is selecting on a given visual graph: INCORRECT Results
Items | Avg1 | Rank1 | Avg2 | Rank2 | Avg3 | Rank3 | Avg4 | Rank4 | Avg5 | Rank5 | Avg6 | Rank6 | Overall Ranking |
Item17 | 9.24 | 5 | 10.00 | 13 | 45.64 | 12 | 45.68 | 12 | 12.33 | 12 | 11 |
EXPECTED Results: Nothing changes when filtered.
Items | Avg1 | Rank1 | Avg2 | Rank2 | Avg3 | Rank3 | Avg4 | Rank4 | Avg5 | Rank5 | Avg6 | Rank6 | Overall Rank |
Item17 | 9.24 | 9 | 10.00 | 18 | 45.64 | 16 | 45.68 | 17 | 12.33 | 18 | 13 |
The end goal is to have a CARD visual that will show the current OVERALL Rank.
I want the Rank to respect any Slicer filters applied.
BUT keep that rank based on the slicer for each item when filtered by a graph on the page. (Funnel or Line graph).
Please let me know what else you might need to help me out.
Hi @malex30 ,
I think that whether you can describe how you calculated the overallrank. In your formula, I think it is the average of total value of ranks. But, what is rank4, rank5, rank6?
According to the table you gave, I calculate it as followings.
But the results are the same.
Please describle it more clearly in order that we can help you better.
Best Regards,
Community Support Team _ xiaosun
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I've updated my original post.
Also If you notice a couple of my RankX want to start with 2 vs 1. Not sure why it's doing that.. For now I have just been adding a "-1" to the end of the formula.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |