Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello
i am trying to use Rankx and matrix table to setup a format in BPI like this
Rank | Territory number | BF3.8 | BF5.0 | BF 5.8 | Total |
1 | 1111 | 10 | 5 | 5 | 20 |
2 | 1112 | 5 | 5 | 5 | 15 |
The closest format i can think of is to use Matrix table. The problem is that Rankx can only be placed as VALUES...so here is what i have now below.
Question 1) any way to move the ranking to the row? so before the territory number column?
Question 2) how to get rid of ranking for each sub product category. i only want to know $$, dont really care about how they rank individually. i do want to keep the overall ranking though
thank you all!!
Solved! Go to Solution.
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!
Hi @leilei787
A slicer will do. But to make it practical I created a new column which I called "Product Category" that returns "BF" for any family code that contains "BF". Code can be adjusted as per your requirement
Product Category =
IF (
CONTAINSSTRING ( 'Table'[ProductFamilyCode], "BF" ),
"BF",
'Table'[ProductFamilyCode]
)
Now your report looks like this
You can use the same link to re-download the file.
hi Tamerj1, one more question, your previous solution works beautifully! thank you!
the only issue now i have is that the ranking works, let's say if i look at 2021 as a whole. but if i select, Jan or March, the ranking wont start from 1,2,3....i think the reason is because the "Total Amount" formula is looking at acumulated amount of what each territory sold. if i select an individual month, ranking remains the same....is there a way to re-rank if i select individual month?
PrimaryGeoCode_c | CurrencyCode | ShippedUSDAmountNet | ProductFamilyCode | Month |
1101 | USD | 2950 | BF3.8 | Jan |
1101 | USD | 1475 | BF3.8 | Jan |
1101 | USD | 2595 | BF3.8 | Feb |
1101 | USD | 1475 | BF5.0 | Apr |
1105 | USD | 1297.5 | BF3.8 | March |
1105 | USD | 1297.5 | BF5.0 | Feb |
1105 | USD | 1450 | BF5.8 | Apr |
1105 | USD | 1297.5 | BF3.8 | Jan |
1105 | USD | 1297.5 | BF5.0 | March |
1110 | USD | 1450 | BF5.8 | Jan |
1110 | USD | 1297.5 | BF3.8 | Jan |
1110 | USD | 1297.5 | BF5.0 | Apr |
1110 | USD | 1450 | BF5.8 | Feb |
1110 | USD | 1700 | BF5.8 | March |
1110 | USD | 1700 | BF5.8 | Feb |
1110 | USD | 1475 | BF5.0 | Feb |
1106 | USD | 5454 | BF5.0 | Apr |
1106 | USD | 24353 | BF5.8 | May |
1106 | USD | 3553 | BF3.8 | May |
1106 | USD | 643 | BF5.0 | May |
1106 | USD | 356 | BF5.8 | Apr |
1101 | USD | 3545 | BF5.8 | Jan |
1101 | USD | 3434 | BF5.8 | Jan |
1101 | USD | 33 | BF5.8 | March |
1101 | USD | 2345 | BF5.0 | March |
1105 | USD | 24334 | BF5.0 | March |
1105 | USD | 235 | BF5.8 | March |
1105 | USD | 33 | BF3.8 | Apr |
Hi @leilei787
Would you help me out please I'm a little confused.
Do you need to consider other product families in the ranking?
"OR"
Do you mean we need to filter out all records that belongs to product families other than BF families. Then do the ranking?
Will the ranking be based on ever total amount, anual total amount or monthly total amount? As this is a calculated column and we have to clearly understand at which granularity our code shall be based on.
Based on the current code, when you select a month you will see the amounts aggregated by month but ranking will not change unless you updated your source data.
If you can share your sample file updated with more data and more rates that would be great.
Hi Tamerj1
sorry for the confusion. below is a more realistic sample.
the goal is to rank territory that sold BF product ( And BF product only. i need to show BF3.0, 5.0 and 5.8 but no need to rank these sub-categories, rank still based on the total). so previous ask was still valid. now the problem is that the ranking also need to be dynamic if i select an individual month. For example, if i select Feb, the rank will be based on BF sales in Feb. If i select Jan to April, the rank will be based on BF sales from Jan to Apr....is it possible?
PrimaryGeoCode_c | CurrencyCode | ShippedUSDAmountNet | ProductFamilyCode | Month |
1101 | USD | $ 34,342 | GVM | 1 |
1101 | USD | $ 5,332 | BF2.5 | 2 |
1101 | USD | $ 355 | BF3.8 | 2 |
1101 | USD | $ 3,556 | BBQ | 3 |
1101 | USD | $ 24,522 | BF5.0 | 4 |
1102 | USD | $ 3,456 | VGN | 1 |
1102 | USD | $ 7,445 | BF5.0 | 2 |
1102 | USD | $ 844 | BF3.8 | 2 |
1102 | USD | $ 985 | GVM | 3 |
1102 | USD | $ 8,980 | BF3.8 | 4 |
1103 | USD | $ 593 | VGN | 1 |
1103 | USD | $ 4,653 | BF3.8 | 2 |
1103 | USD | $ 3,353 | BF5.0 | 2 |
1103 | USD | $ 2,432 | GVM | 3 |
1103 | USD | $ 2,556 | BF3.8 | 4 |
1104 | USD | $ 4,342 | BF5.0 | 1 |
1104 | USD | $ 56,245 | BF3.8 | 2 |
1104 | USD | $ 9,543 | VGN | 2 |
1104 | USD | $ 29,405 | BBQ | 3 |
1104 | USD | $ 34,532 | BF5.0 | 4 |
Hi @leilei787
In this case, it has to be a measure that is your first solution. Again we will have the problems of your original query hence back to square 1.
So I would advise to go back to your original measure and try to manually hide the ranking for subtotals by minimizing the respective column size to zero width with your mouse. Shifting the row grand total to the left is not possible.
Finally, you can filter out all product families other than "BF" by creating a new table using simple code
Table 2 =
FILTER (
'Table',
CONTAINSSTRING ( 'Table'[ProductFamilyCode], "BF" )
)
Then use the new table instead for your analysis.
Please let me know if anything further I can help you with.
Have a great day!
Hi @leilei787
Best to create a new calculated column to rank your products. Then you can just drag it to rows or otherwise use the following measure which will show blanks for any higher granularity.
IF (
HASONEVALUE ( Producr Key ),
VALUES ( "RANKX calculation" )
)
If you just want to to hide the subcategories just resize the column using the mouse to zero width.
it would be great if you can share a sample file to assesst further.
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!
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
38 | |
25 | |
20 | |
15 | |
8 |
User | Count |
---|---|
69 | |
47 | |
46 | |
20 | |
16 |