Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Calculating Rank over multiple tables and multiple columns

Short Version I am trying to calculate the rank of production facilities based on each unique combination of materials by month for every year. an example of my data is : 

 

What I'm trying to achieve is: 

 

 

Facility, Product, Year, Month and Samples are in a table "A"

Widget_1_Source, Widget_1_Type, Widget_1_Amount, Widget_2_Source, Widget_3_Source, Widget_4_Source and Widget_5_Source are all in table "B"

 

Table A is related to B via a column called Product_Sequence (the numeric serial number for each Product "ID" which is free text)

 

I have calculated an explicit measure for Widget_1_Amount via: 

CALCULATE(MAX(B[Widget_1_Amount]), ALLEXCEPT(B, B[Product_Sequence],B[Widget_1_Type], MXD_Cement[Widget_1_Source], MXD_Cement[Widget_2_Source], MXD_Cement[Widget_3_Source], MXD_Cement[Widget_4_Source], MXD_Cement[Widget_5_Source] ) )
 
I have tried almost every published permutation of rankx with no success (I will not waste space with all the examples that didnt work). 
 
If anyone can offer a suggestion on how to achieve the desired result I would greatly appreciate it. 

 

 

Need Help

1 Reply

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ，

    From your image and description, I can't tell which fields in the table in the first image you're trying to output are sorted by.Could you pls tell more details ?

     

    Best Regards

    Lucien