Forum Discussion
Question about Rankx in Matrix table format
- 4 years ago
Hi leilei787
Here is the sample file with the solution https://www.dropbox.com/t/ic5uywEC976ZR0zmYou need to create two columns.
Total Amount = VAR CurrentCode = 'Table'[PrimaryGeoCode_c] VAR CurrentCodeTable = FILTER ( 'Table', 'Table'[PrimaryGeoCode_c] = CurrentCode ) VAR Results = SUMX ( CurrentCodeTable, 'Table'[ShippedUSDAmountNet] ) RETURN ResultsRanking = RANKX ( 'Table', 'Table'[Total Amount], , DESC, Dense )Then create your measure
Shipping Net = SUM ('Table'[ShippedUSDAmountNet] )I hope this satisfies your requirement. If so please consider marking this reply as "Accepted" solution. Thank you!
thank you! somehow i could not get it work even i use calculated column. here is what i did
i created a calculated column, here is my formula:
here are the sample data:
so ideally i want to have the rank first, then territory numbers, product family on the column. Rank by total sales, not individual product.
| PrimaryGeoCode_c | CurrencyCode | ShippedUSDAmountNet | ProductFamilyCode |
| 1101 | USD | 2950 | BF3.8 |
| 1101 | USD | 1475 | BF3.8 |
| 1101 | USD | 2595 | BF3.8 |
| 1101 | USD | 1475 | BF5.0 |
| 1105 | USD | 1297.5 | BF3.8 |
| 1105 | USD | 1297.5 | BF5.0 |
| 1105 | USD | 1450 | BF5.8 |
| 1105 | USD | 1297.5 | BF3.8 |
| 1105 | USD | 1297.5 | BF5.0 |
| 1110 | USD | 1450 | BF5.8 |
| 1110 | USD | 1297.5 | BF3.8 |
| 1110 | USD | 1297.5 | BF5.0 |
| 1110 | USD | 1450 | BF5.8 |
| 1110 | USD | 1700 | BF5.8 |
| 1110 | USD | 1700 | BF5.8 |
| 1110 | USD | 1475 | BF5.0 |
| 1106 | USD | 5454 | BF5.0 |
| 1106 | USD | 24353 | BF5.8 |
| 1106 | USD | 3553 | BF3.8 |
| 1106 | USD | 643 | BF5.0 |
| 1106 | USD | 356 | BF5.8 |
| 1106 | USD | 665 | BF5.8 |
Hi leilei787
Here is the sample file with the solution https://www.dropbox.com/t/ic5uywEC976ZR0zm
You need to create two columns.
Total Amount =
VAR CurrentCode = 'Table'[PrimaryGeoCode_c]
VAR CurrentCodeTable =
FILTER (
'Table',
'Table'[PrimaryGeoCode_c] = CurrentCode
)
VAR Results =
SUMX (
CurrentCodeTable,
'Table'[ShippedUSDAmountNet]
)
RETURN
Results Ranking =
RANKX (
'Table',
'Table'[Total Amount],
,
DESC,
Dense
)Then create your measure
Shipping Net =
SUM ('Table'[ShippedUSDAmountNet] )I hope this satisfies your requirement. If so please consider marking this reply as "Accepted" solution. Thank you!
- leilei7874 years agoHelper II
hi Tamerj1
your method works thank you!...but i need to make it a little complicated....
in the last dataset, i have product category BF3.8, BF5.0 and BF5.8. what if i have more product families? but in the end, i just want to rank BF3.8, BF 5.0 and BF5.8?
I know i need to modify Total amount formula you wrote....but keep failing
so again...same result needed for BF category only. thank you so much!
PrimaryGeoCode_c CurrencyCode ShippedUSDAmountNet ProductFamilyCode 1101 USD 2950 BF3.8 1101 USD 1475 BF3.8 1101 USD 2595 BF3.8 1101 USD 1475 BF5.0 1105 USD 1297.5 BF3.8 1105 USD 1297.5 BF5.0 1105 USD 1450 BF5.8 1105 USD 1297.5 BF3.8 1105 USD 1297.5 BF5.0 1110 USD 1450 BF5.8 1110 USD 1297.5 BF3.8 1110 USD 1297.5 BF5.0 1110 USD 1450 BF5.8 1110 USD 1700 BF5.8 1110 USD 1700 BF5.8 1110 USD 1475 BF5.0 1106 USD 5454 BF5.0 1106 USD 24353 BF5.8 1106 USD 3553 BF3.8 1106 USD 643 BF5.0 1106 USD 356 BF5.8 1101 USD 3545 GVM 1101 USD 3434 GVM 1101 USD 33 GVM 1101 USD 2345 GVM 1105 USD 24334 GVM 1105 USD 235 BBQ 1105 USD 33 BBQ 1105 USD 3555 BBQ 1105 USD 33 BBQ 1110 USD 664 BBQ 1110 USD 2455 BBQ 1110 USD 433 BBQ 1110 USD 4325 VGN 1110 USD 4533 VGN 1110 USD 2456 VGN 1110 USD 3324 VGN 1106 USD 356 VGN 1106 USD 35267 VGN 1106 USD 868 VGN 1106 USD 907 VGN 1106 USD 589 VGN