RANKX based on 2 related columns

[Anonymous]

I have Rank calculated with the below 

Rank:= RANKX(ALLSELECTED('Product Dim'[Product Name]),[SALEAmount($)], ,DESC)

Now, I have an 'Account Dim' which contains the 'Account Name' field. Both these tables are related with 'Fact' table, and I can create a Table visual with Product Name, Account Name, and SaleAmount.

I want to create a Rank measure for the sale amount, based on the Product Name and Account Name combinations. When I create a visual with Product name, Saleamount; I am able to use the above 'Rank' measure appropriately, but how can I do it with Account name combination as well ? 

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

First, in fac table,  you get the 'Account Name' column and 'Product Name' column using the formulas.

Account Name=RELATED('Account Dim'[Account Name])
Product Name=RELATED('Product Dim'[Product Name])

Then create a measure using the following formula and check if it is successful. 

Rank! = RANKX(ALLSELECTED(FactTable),CALCULATE(SUM(FactTable[SALEAmount]),ALLEXCEPT(FACTTABLE,'FactTable'[Product name],'FactTable'[Account Name])),,DESC)

If this is not what you want, please share your sample data or .pbix file for further analysis.

Best Regards,
Angelia

[Anonymous]

Hi @v-huizhn-msft thanks for your response. This method makes sense, and I think it should work. However there are 2 issues that I am facing, while trying this.

1. I am unable to use 'RELATED' function to create these 2 columns. Account DIM and Product Dim are related with FactTable (with one to Many rel). However, when I try to create a new column with this syntax, it wont except.

2. I assume that the RANKX syntax will Rank the sale amount for all Products and Accounts on Fact table ?

Rank! = RANKX(ALLSELECTED(FactTable),CALCULATE(SUM(FactTable[SALEAmount]),ALLEXCEPT(FACTTABLE,'FactTable'[Product name],'FactTable'[Account 

I want it to work with filters, and rank should be based on the selected products/accounts. What would happen if I use 'ALLSELECTED' instead of 'ALLEXPECT'.

Regards,

PS. I am in transition phase from MS Excel to PowerBI, hence please dont mind if my questions are silly.

[v-huizhn-msft]

Hi @Anonymous,

For Account DIM and Product Dim are related with FactTable (with one to Many rel), one account or Product Dimeyou relate to many rows in FactTable, right? If it is, you can use Related function in FactTable. You need to rank sales amout based on products/accounts, you need to use ALLEXCEPT.

Thanks,
Angelia

[sokg]

I think you can create a calculated column in your fact table

RANKX based on 2 related columns = RANKX (

                                                                            FILTER ( FACT TABLE ,

                                                                                'Fact Table[Product name] = EARLIER(Fact Table[Product name])

                                                                                &&  Fact Table[Account Name] = EARLIER( Fact Table[Account Name] )

                                                                            ),

                                                                     [SALEAmount($), , DESC)

Check this link https://www.sqlbi.com/articles/how-to-compute-index-numbers-at-top-speed/

[Anonymous]

@sokg - thanks for your response. After I try this, i get one circular error warning (A circular dependency was detected), and column is not calculated. Any other way to do this ?

And thanks for sharing that useful link, I will give it a read.

[sokg]

@Anonymous - Sorry, I can't think anything else.