Forum Discussion

ddpl's avatar
ddpl
Icon for Solution Sage rankSolution Sage
2 years ago

RankX for each row in Matrix from two tables...???

I have two tables

Sale (3 rows: criteria, sold, dayDiff)

Tab2 (1 row: day: containing '3Days', '7Days', '15Days', '30Days')

No relationship between those two, obviously.

 

I want rank for each row but my 'rank' measure is not gave me right result, the measure gave same value 1 for every cell

 

measure 1 : 

value = 

var _sv = SELECTEDVALUE(Tab2[day])
return
SWITCH(
    TRUE(),
    _sv = "3 Days", CALCULATE(SUM(Sale[sold]), Sale[dayDiff] <= 3),
    _sv = "7 Days", CALCULATE(SUM(Sale[sold]), Sale[dayDiff] <= 7),
    _sv = "15 Days", CALCULATE(SUM(Sale[sold]), Sale[dayDiff] <= 15),
    _sv = "30 Days", CALCULATE(SUM(Sale[sold]), Sale[dayDiff] <= 30)
)
 
measure 2 :
rank =
IF(
    NOT(ISBLANK([value])),
    RANKX(
        CALCULATETABLE(
            ALLSELECTED(Tab2[day])
        ),
        [value],,,Dense
    )
)
 
I need a matrix like below (switch values to rows = ON)
criteria   3Days   7Days   15Days   30Days
A    
value   100   300   500   50
rank   3   2   1   4
B    
value   400   200   800   1000
rank   3   4   2   1
C    
value   70   500   450   600
rank   4   2   3   1
Need Help
Show and Tell
Tips and Tricks

1 Reply

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    2 years ago

    Hey ddpl ,

     

    please prepare a pbix file containing sample data that still reflects your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix to OneDrive, Google Drive, of Dropbox and share the link.

    If you are using a spreadsheet to create the sample data, share the spreadsheet as well.

     

    Can you please explain what you are ranking and how this does relate to the binning?

     

    Regards,

    Tom