SSAS Tabular - DAX Dynamic Ranking measure with slicer

Question

I'm searching for a measure to utilize within SSAS Tabular model that will me to perform dynamic ranking that will automatically update the associated rank value based on filters and slicer values that are applied.

I am not in this kind of scearios : PowerPivot DAX - Dynamic Ranking Per Group (Min Per Group)

The difference is the following, my data are not in the same table : I have a fact table like this :

-------------------------------------------------------------------------------------
ClientID | ProductID | Transaction Date | Sales
------------------------------------------------------------------------------------
C1            P3        1/1/2012          $100
C2            P1        8/1/2012          $150
C3            P4        9/1/2012          $200
C1            P2        3/5/2012          $315
C2            P2        9/5/2012          $50         
C3            P2        12/9/2012         $50
------------------------------------------------------------------------------------

A Customer table

-------------------------------------------------------------------------------------
    ClientID   | ClientCountry |
    C1           France     
    C2           France
    C3           Germany
------------------------------------------------------------------------------------

...and also a Product table

-------------------------------------------------------------------------------------
    ProductID   | ProductSubCategory |
    P1             SB1    
    P2             SB1
    P3             SB2
    P4             SB3
------------------------------------------------------------------------------------

So here is my visualization pivot table :

-------------------------------------------------------------------------------------
         ProductSubCategory | Sales
         SB1                  565 (150 + 315 + 50 + 50)
         SB2                  100
         SB3                  200

And the measure I'm looking for should perform like this :

-------------------------------------------------------------------------------------
         ProductSubCategory | Sales                      | Rank
         SB1                  565 (150 + 315 + 50 + 50)    1
         SB2                  100                          3
         SB3                  200                          2

...simple, I browse my cube into Excel, put the ProductSubCategory in line, sum of Sales and expect my measure gives me correct ranking by ProductSubCategory.

Now, scenario also includes using a slicer on ClientCountry.

So when I select 'France', I expect my measure gives me an adapted ranking, only including ProductSubCategory for Clients living in France (so C1 and C2).

I tried a lot of solutions but without any result. Has anyone and idea with this kind of scenario ?

I greatly appreciate your help with this!

Thank's all

SSAS Tabular - DAX Dynamic Ranking measure with slicer

So here is my visualization pivot table :

0 Answers0