Question about Rankx in Matrix table format

[leilei787]

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!!

[tamerj1]

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.

[leilei787]

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

[tamerj1]

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.

[leilei787]

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

[tamerj1]

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!

[tamerj1]

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. 

[leilei787]

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: 

Bflex TM Ranking3 = rankx(ALLSELECTED('vw_fact_ShippingDetailWithReturns (Commercial)'[PrimaryGeoCode_c]),[Total Shipping Net])

i am able to put the rank on the ROWs....however, in the matrix format, it is still not working right

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

[tamerj1 · Accepted 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!

[leilei787]

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