TheMacroNoob
Board Regular
- Joined
- Aug 5, 2022
- Messages
- 52
- Office Version
- 365
- Platform
- Windows
Hello Experts,
I have been struggling for hours with different variations of this DAX formula in Power BI:
I am trying to rank the RMs from PDB[RM], if their property is "Stable" in the column PDB[Simplified Portfolio], based on the average of table column Shops[Report Total].
- If there average is blank (due to no data for any of the selected RM's properties in Shops), then return 1. (If they have no data, then they had no shops, which would be rank #1)
- If an RM has no stable properties, then I would want them excluded from the ranking altogether.
There is a connection between PDB and Shops on the Property ID column, works as expected.
How do I achieve this with a measure, or with calculated tables? I created a couple calculated tables that accomplish everything I want, but the relationship can't be connected due to circular reference, apparently.
I just want to display the selected RM's rank in a card.
Here are samples of the data:
I have been struggling for hours with different variations of this DAX formula in Power BI:
Power Query:
Rank Shops =
CALCULATE(
RANKX(
ALL(PDB[RM]),
CALCULATE(AVERAGE(Shops[Report Total])),
,
ASC,
Skip
),
PDB[Simplified Portfolio] = "Stable"
)I am trying to rank the RMs from PDB[RM], if their property is "Stable" in the column PDB[Simplified Portfolio], based on the average of table column Shops[Report Total].
- If there average is blank (due to no data for any of the selected RM's properties in Shops), then return 1. (If they have no data, then they had no shops, which would be rank #1)
- If an RM has no stable properties, then I would want them excluded from the ranking altogether.
There is a connection between PDB and Shops on the Property ID column, works as expected.
How do I achieve this with a measure, or with calculated tables? I created a couple calculated tables that accomplish everything I want, but the relationship can't be connected due to circular reference, apparently.
I just want to display the selected RM's rank in a card.
Here are samples of the data:
| terest.xlsx | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | |||
| 3 | Result (Without Excluding VP names): | Shops | PDB | ||||||||||
| 4 | RM | Average Shop | RANK | Property ID | Report Total | Property ID | RM | VP | Simplified Portfolio | ||||
| 5 | Am | 1 | 1 | 422 | 58.90% | 107 | Miss | Bri | Stable | ||||
| 6 | Ash | 0.935 | 23 | 433 | 78.40% | 109 | Miss | Bri | Stable | ||||
| 7 | Beck | 0.892 | 24 | 433 | 92.40% | 110 | Cind | Janet | Stable | ||||
| 8 | Brand | 0.646 | 34 | 439 | 72.40% | 121 | Nak | Dav | Stable | ||||
| 9 | Caro | 1 | 1 | 450 | 80.50% | 165 | Miss | Bri | Stable | ||||
| 10 | Chad | 1 | 1 | 452 | 84.90% | 171 | Unknown | Unknown | None | ||||
| 11 | Cind | 1 | 1 | 473 | 82.70% | 173 | Unknown | Unknown | None | ||||
| 12 | Dan | 1 | 1 | 475 | 89.20% | 175 | Unknown | Unknown | None | ||||
| 13 | Dav | 1 | 1 | 484 | 94.60% | 328 | Ren | Janet | Stable | ||||
| 14 | Dem | 1 | 1 | 625 | 88.60% | 331 | Ren | Janet | Stable | ||||
| 15 | Gab | 0.724 | 33 | 649 | 56.80% | 407 | Lac | Jam | Stable | ||||
| 16 | Gin | 1 | 1 | 664 | 86.50% | 409 | Nak | Dav | Stable | ||||
| 17 | Hol | 1 | 1 | 679 | 87.00% | 410 | Miss | Bri | Stable | ||||
| 18 | Jam | 1 | 1 | 697 | 97.80% | 414 | Nak | Dav | Stable | ||||
| 19 | Kel | 0.859 | 28 | 801 | 80.50% | 415 | Nak | Dav | Stable | ||||
| 20 | Lac | 0.827 | 30 | 842 | 80.50% | 416 | Ther | Janet | Stable | ||||
| 21 | Lar | 0.865 | 27 | 857 | 79.50% | 417 | Nak | Dav | Stable | ||||
| 22 | Leo | 1 | 1 | 864 | 51.90% | 418 | Nak | Dav | Stable | ||||
| 23 | Mar | 1 | 1 | 961 | 92.40% | 419 | Ther | Janet | Stable | ||||
| 24 | Mel | 0.827 | 30 | 966 | 85.90% | 422 | Ther | Janet | Stable | ||||
| 25 | Mia | 1 | 1 | 970 | 72.40% | 423 | Ther | Janet | Stable | ||||
| 26 | Mic | 0.832 | 29 | 979 | 83.20% | 424 | Lac | Jam | Stable | ||||
| 27 | Miss | 1 | 1 | 985 | 93.50% | 425 | Cind | Janet | Stable | ||||
| 28 | Nak | 1 | 1 | 426 | Mic | Janet | Stable | ||||||
| 29 | Oct | 1 | 1 | 427 | Cind | Janet | Stable | ||||||
| 30 | Ren | 1 | 1 | 428 | Miss | Bri | Stable | ||||||
| 31 | Rich | 0.886 | 25 | 429 | Oct | Bri | Stable | ||||||
| 32 | Rob | 1 | 1 | 430 | Unknown | Bri | Transition | ||||||
| 33 | Sar | 1 | 1 | 431 | Gin | Hol | Transition | ||||||
| 34 | Tery | 1 | 1 | 432 | Gin | Hol | Transition | ||||||
| 35 | Ther | 0.7675 | 32 | 433 | Mia | Nicki | Transition | ||||||
| 36 | Trac | 0.87 | 26 | 439 | Brand | Nicki | Stable | ||||||
| 37 | Unknown | 1 | 1 | 440 | Unknown | Unknown | None | ||||||
| 38 | Yve | 1 | 1 | 443 | Unknown | Unknown | None | ||||||
| 39 | 444 | Am | Bri | Stable | |||||||||
| 40 | 445 | Oct | Bri | Stable | |||||||||
| 41 | 446 | Oct | Bri | Stable | |||||||||
| 42 | 447 | Dan | Dav | Transition | |||||||||
| 43 | 448 | Tery | Bri | Stable | |||||||||
| 44 | 449 | Mia | Nicki | Transition | |||||||||
| 45 | 450 | Lac | Jam | Stable | |||||||||
| 46 | 452 | Lac | Jam | Stable | |||||||||
| 47 | 453 | Lac | Jam | Stable | |||||||||
| 48 | 461 | Mia | Nicki | Stable | |||||||||
| 49 | 462 | Mia | Nicki | Stable | |||||||||
| 50 | 463 | Ther | Janet | Stable | |||||||||
| 51 | 464 | Ther | Janet | Stable | |||||||||
| 52 | 465 | Ther | Janet | Stable | |||||||||
| 53 | 466 | Ther | Janet | Stable | |||||||||
| 54 | 469 | Ther | Janet | Stable | |||||||||
| 55 | 473 | Mel | Dav | Stable | |||||||||
| 56 | 475 | Beck | Jam | Stable | |||||||||
| 57 | 476 | Mia | Nicki | Stable | |||||||||
| 58 | 477 | Gab | Jam | Stable | |||||||||
| 59 | 480 | Unknown | Unknown | None | |||||||||
| 60 | 484 | Ther | Janet | Stable | |||||||||
| 61 | 488 | Tery | Bri | Stable | |||||||||
| 62 | 498 | Miss | Bri | Stable | |||||||||
| 63 | 499 | Unknown | Nicki | Stable | |||||||||
| 64 | 505 | Lac | Jam | Stable | |||||||||
| 65 | 508 | Rich | Bri | Stable | |||||||||
| 66 | 510 | Kel | Janet | Stable | |||||||||
| 67 | 514 | Lac | Jam | Stable | |||||||||
| 68 | 516 | Rob | Jam | Stable | |||||||||
| 69 | 517 | Beck | Jam | Stable | |||||||||
| 70 | 520 | Brand | Nicki | Stable | |||||||||
| 71 | 527 | Oct | Bri | Stable | |||||||||
| 72 | 529 | Ther | Janet | Stable | |||||||||
| 73 | 530 | Mel | Dav | Stable | |||||||||
| 74 | 547 | Mic | Janet | Stable | |||||||||
| 75 | 583 | Kel | Janet | Stable | |||||||||
| 76 | 600 | Trac | Janet | Stable | |||||||||
| 77 | 601 | Mic | Janet | Stable | |||||||||
| 78 | 602 | Beck | Jam | Stable | |||||||||
| 79 | 615 | Sar | Dav | Stable | |||||||||
| 80 | 617 | Brand | Nicki | Stable | |||||||||
| 81 | 619 | Dan | Dav | Stable | |||||||||
| 82 | 620 | Lac | Jam | Stable | |||||||||
| 83 | 624 | Dan | Dav | Stable | |||||||||
| 84 | 625 | Rich | Bri | Stable | |||||||||
| 85 | 627 | Lar | Dav | Stable | |||||||||
| 86 | 629 | Gab | Jam | Transition | |||||||||
| 87 | 630 | Ren | Janet | Stable | |||||||||
| 88 | 631 | Ther | Janet | Stable | |||||||||
| 89 | 632 | Ren | Janet | Stable | |||||||||
| 90 | 633 | Oct | Bri | Stable | |||||||||
| 91 | 636 | Tery | Bri | Stable | |||||||||
| 92 | 637 | Miss | Bri | Stable | |||||||||
| 93 | 638 | Gin | Hol | Stable | |||||||||
| 94 | 640 | Kel | Janet | Stable | |||||||||
| 95 | 641 | Rich | Bri | Stable | |||||||||
| 96 | 642 | Rich | Bri | Stable | |||||||||
| 97 | 644 | Sar | Dav | Stable | |||||||||
| 98 | 645 | Oct | Bri | Stable | |||||||||
| 99 | 646 | Oct | Bri | Stable | |||||||||
| 100 | 649 | Brand | Nicki | Stable | |||||||||
| 101 | 650 | Mia | Nicki | Stable | |||||||||
| 102 | 651 | Nak | Dav | Stable | |||||||||
| 103 | 653 | Leo | Bri | Stable | |||||||||
| 104 | 654 | Leo | Bri | Stable | |||||||||
| 105 | 656 | Am | Bri | Stable | |||||||||
| 106 | 657 | Am | Bri | Stable | |||||||||
| 107 | 658 | Chad | Dav | Stable | |||||||||
| 108 | 659 | Lar | Dav | Stable | |||||||||
| 109 | 660 | Ren | Janet | Stable | |||||||||
| 110 | 661 | Leo | Bri | Stable | |||||||||
| 111 | 663 | Mia | Nicki | Stable | |||||||||
| 112 | 664 | Lar | Dav | Stable | |||||||||
| 113 | 667 | Ren | Janet | Stable | |||||||||
| 114 | 668 | Am | Bri | Stable | |||||||||
| 115 | 669 | Ash | Janet | Stable | |||||||||
| 116 | 670 | Hol | Hol | None | |||||||||
| 117 | 672 | Mia | Nicki | Stable | |||||||||
| 118 | 673 | Ash | Janet | Stable | |||||||||
| 119 | 675 | Mia | Nicki | Stable | |||||||||
| 120 | 677 | Beck | Jam | Stable | |||||||||
| 121 | 678 | Kel | Janet | Stable | |||||||||
| 122 | 679 | Trac | Janet | Stable | |||||||||
| 123 | 680 | Kel | Janet | Stable | |||||||||
| 124 | 681 | Trac | Janet | Stable | |||||||||
| 125 | 682 | Trac | Janet | Stable | |||||||||
| 126 | 684 | Mel | Dav | Stable | |||||||||
| 127 | 686 | Mel | Dav | Transition | |||||||||
| 128 | 687 | Mar | Dav | Stable | |||||||||
| 129 | 690 | Mar | Hol | Transition | |||||||||
| 130 | 691 | Mar | Dav | Transition | |||||||||
| 131 | 692 | Caro | Bri | Transition | |||||||||
| 132 | 693 | Caro | Hol | Transition | |||||||||
| 133 | 694 | Gab | Jam | Stable | |||||||||
| 134 | 695 | Gin | Hol | Stable | |||||||||
| 135 | 696 | Brand | Nicki | Transition | |||||||||
| 136 | 697 | Trac | Janet | Transition | |||||||||
| 137 | 698 | Gin | Hol | Transition | |||||||||
| 138 | 700 | Mic | Janet | Stable | |||||||||
| 139 | 704 | Ren | Janet | Stable | |||||||||
| 140 | 707 | Kel | Janet | Stable | |||||||||
| 141 | 708 | Ren | Janet | Stable | |||||||||
| 142 | 711 | Ren | Janet | Stable | |||||||||
| 143 | 713 | Cind | Janet | Stable | |||||||||
| 144 | 719 | Cind | Janet | Stable | |||||||||
| 145 | 726 | Cind | Janet | Stable | |||||||||
| 146 | 734 | Cind | Janet | Stable | |||||||||
| 147 | 742 | Ther | Janet | Stable | |||||||||
| 148 | 753 | Mic | Janet | Stable | |||||||||
| 149 | 762 | Ash | Janet | Stable | |||||||||
| 150 | 776 | Ren | Janet | Stable | |||||||||
| 151 | 800 | Gin | Hol | Transition | |||||||||
| 152 | 801 | Gin | Hol | Transition | |||||||||
| 153 | 803 | Brand | Nicki | Transition | |||||||||
| 154 | 804 | Yve | Dav | Transition | |||||||||
| 155 | 809 | Lar | Hol | Transition | |||||||||
| 156 | 810 | Tery | Bri | Transition | |||||||||
| 157 | 811 | Dav | Dav | Transition | |||||||||
| 158 | 812 | Unknown | Bri | Transition | |||||||||
| 159 | 813 | Unknown | Bri | Transition | |||||||||
| 160 | 814 | Dav | Dav | Transition | |||||||||
| 161 | 815 | Am | Bri | Transition | |||||||||
| 162 | 821 | Lar | Dav | Transition | |||||||||
| 163 | 822 | Lar | Dav | Transition | |||||||||
| 164 | 823 | Dem | Jam | Transition | |||||||||
| 165 | 825 | Mia | Nicki | Transition | |||||||||
| 166 | 827 | Rob | Jam | Transition | |||||||||
| 167 | 828 | Unknown | Nicki | Stable | |||||||||
| 168 | 829 | Leo | Bri | Stable | |||||||||
| 169 | 830 | Leo | Bri | Stable | |||||||||
| 170 | 831 | Unknown | Nicki | Transition | |||||||||
| 171 | 834 | Caro | Bri | Transition | |||||||||
| 172 | 835 | Caro | Bri | Transition | |||||||||
| 173 | 836 | Am | Bri | Transition | |||||||||
| 174 | 837 | Sar | Dav | Transition | |||||||||
| 175 | 838 | Unknown | Bri | Transition | |||||||||
| 176 | 840 | Dem | Jam | Transition | |||||||||
| 177 | 841 | Mia | Nicki | Transition | |||||||||
| 178 | 842 | Mia | Nicki | Transition | |||||||||
| 179 | 843 | Dem | Jam | Transition | |||||||||
| 180 | 844 | Rich | Bri | Transition | |||||||||
| 181 | 848 | Yve | Dav | Transition | |||||||||
| 182 | 850 | Rob | Jam | Transition | |||||||||
| 183 | 851 | Rob | Jam | Transition | |||||||||
| 184 | 857 | Am | Bri | Transition | |||||||||
| 185 | 864 | Yve | Dav | Transition | |||||||||
| 186 | 865 | Yve | Dav | Transition | |||||||||
| 187 | 868 | Unknown | Unknown | Transition | |||||||||
| 188 | 876 | Brand | Nicki | Transition | |||||||||
| 189 | 877 | Yve | Dav | Transition | |||||||||
| 190 | 878 | Leo | Bri | Transition | |||||||||
| 191 | 902 | Dan | Dav | Stable | |||||||||
| 192 | 905 | Sar | Dav | Stable | |||||||||
| 193 | 912 | Dan | Dav | Stable | |||||||||
| 194 | 914 | Yve | Dav | Stable | |||||||||
| 195 | 925 | Mar | Dav | Stable | |||||||||
| 196 | 926 | Mar | Dav | Stable | |||||||||
| 197 | 942 | Dan | Dav | Stable | |||||||||
| 198 | 944 | Mar | Dav | Stable | |||||||||
| 199 | 945 | Dan | Dav | Stable | |||||||||
| 200 | 948 | Lar | Dav | Stable | |||||||||
| 201 | 949 | Rich | Bri | Stable | |||||||||
| 202 | 957 | Tery | Bri | Stable | |||||||||
| 203 | 958 | Dem | Jam | Stable | |||||||||
| 204 | 959 | Beck | Jam | Stable | |||||||||
| 205 | 961 | Brand | Nicki | Transition | |||||||||
| 206 | 963 | Hol | Hol | Transition | |||||||||
| 207 | 965 | Jam | Jam | Transition | |||||||||
| 208 | 966 | Kel | Janet | Stable | |||||||||
| 209 | 967 | Mel | Dav | Stable | |||||||||
| 210 | 968 | Lar | Dav | Transition | |||||||||
| 211 | 969 | Gab | Jam | Stable | |||||||||
| 212 | 970 | Gab | Jam | Stable | |||||||||
| 213 | 971 | Gab | Jam | Transition | |||||||||
| 214 | 972 | Unknown | Unknown | None | |||||||||
| 215 | 975 | Sar | Dav | Stable | |||||||||
| 216 | 978 | Rob | Jam | Stable | |||||||||
| 217 | 979 | Mic | Janet | Stable | |||||||||
| 218 | 980 | Cind | Janet | Stable | |||||||||
| 219 | 981 | Nak | Dav | Stable | |||||||||
| 220 | 982 | Cind | Janet | Stable | |||||||||
| 221 | 983 | Ash | Janet | Stable | |||||||||
| 222 | 984 | Mic | Janet | Stable | |||||||||
| 223 | 985 | Ash | Janet | Stable | |||||||||
| 224 | 986 | Sar | Dav | Stable | |||||||||
| 225 | 987 | Kel | Janet | Stable | |||||||||
| 226 | 988 | Trac | Janet | Stable | |||||||||
| 227 | 989 | Trac | Janet | Stable | |||||||||
| 228 | 990 | Cind | Janet | Stable | |||||||||
| 229 | 991 | Gin | Hol | Transition | |||||||||
| 230 | 994 | Sar | Dav | Stable | |||||||||
| 231 | 997 | Mar | Dav | Stable | |||||||||
| 232 | 998 | Am | Bri | Stable | |||||||||
| 233 | 999 | Mar | Dav | Stable | |||||||||
Testing Ground | |||||||||||||
Last edited:
