Forum Discussion

newyearrrr's avatar
newyearrrr
Frequent Visitor
2 years ago
Solved

could you help me dynamic ranking data with measure

------- > for power bi file example data 

i have data table like this :

 

and I want to sort the data dynamically with this measure. (Card A shown in the picture below)

A = CALCULATE( SUM('03Data'[VALUE_(MB)] ) , 
        FILTER('03Data','03Data'[GROUP] = "Forecast") ,
 ALLEXCEPT('03Data_FruitExport_LOT','03Data_FruitExport_LOT'[COUNTRY] )))

if card A >= 0 , I want to sort them from positive to negative ( by FC_index01 : from + to - )
but if card A < 0 , I want to sort them from negative to positive ( by FC_index02 : from - to + )


I tried but couldn't.
Sometimes the sorting will be wrong when I select the filter Export_Fruit.



------- > for power bi file example data

Need Help
  • Alex87's avatar
    Alex87
    2 years ago

    Hello,

     

    I changed your slicer export fruit to come from Table 2. idem Country from Table

    I reviewed your formulas:

     

     

     

    _Rank1 = 
RANKX(
    ALLSELECTED('03Data'),
    CALCULATE(SUM('03Data'[VALUE_(MB)])),
    ,
    ASC,
    Dense
)

_Rank2 = 
RANKX(
    ALLSELECTED('03Data'),
    CALCULATE(SUM('03Data'[VALUE_(MB)])),
    ,
    DESC,
    Dense
)

SortSolution = 
VAR _SelectedFruit = SELECTEDVALUE(Table2[EXPORT_FRUIT])
VAR _IsSelectedFuit = ISFILTERED(Table2[EXPORT_FRUIT])
VAR _SelectedCountry = SELECTEDVALUE('Table'[COUNTRY])
VAR _IsSelectedCountry = ISFILTERED('Table'[COUNTRY])
VAR _Value = 
SWITCH(
    TRUE(),
    _IsSelectedFuit && NOT(_IsSelectedCountry), CALCULATE([SUMVAL_FC], ALL('03Data'[COUNTRY]), ALL('03Data'[COUNTRY : LOT]), '03Data'[EXPORT_FRUIT] = _SelectedFruit),
    _IsSelectedFuit && _IsSelectedCountry, CALCULATE([SUMVAL_FC], ALL('03Data'[COUNTRY]), ALL('03Data'[COUNTRY : LOT]),'03Data'[COUNTRY] = _SelectedCountry, '03Data'[EXPORT_FRUIT] = _SelectedFruit),
    NOT(_IsSelectedFuit) && _IsSelectedCountry, CALCULATE([SUMVAL_FC], ALL('03Data'),ALL('03Data'[COUNTRY : LOT]),'03Data'[COUNTRY] = _SelectedCountry),
    CALCULATE([SUMVAL_FC], ALL('03Data'[COUNTRY : LOT]), ALL('03Data'[EXPORT_FRUIT]))) 


VAR _Result = 
IF(_Value>=0, [_Rank2],[_Rank1])

RETURN
_Result

     

     

     

     

    you can filter our in the graph the 0 values that are not useful

    If it answers your question, please mark my reply as the solution. Thanks

     

2 Replies

  • Alex87's avatar
    Alex87
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Hello,

     

    I changed your slicer export fruit to come from Table 2. idem Country from Table

    I reviewed your formulas:

     

     

     

    _Rank1 = 
RANKX(
    ALLSELECTED('03Data'),
    CALCULATE(SUM('03Data'[VALUE_(MB)])),
    ,
    ASC,
    Dense
)

_Rank2 = 
RANKX(
    ALLSELECTED('03Data'),
    CALCULATE(SUM('03Data'[VALUE_(MB)])),
    ,
    DESC,
    Dense
)

SortSolution = 
VAR _SelectedFruit = SELECTEDVALUE(Table2[EXPORT_FRUIT])
VAR _IsSelectedFuit = ISFILTERED(Table2[EXPORT_FRUIT])
VAR _SelectedCountry = SELECTEDVALUE('Table'[COUNTRY])
VAR _IsSelectedCountry = ISFILTERED('Table'[COUNTRY])
VAR _Value = 
SWITCH(
    TRUE(),
    _IsSelectedFuit && NOT(_IsSelectedCountry), CALCULATE([SUMVAL_FC], ALL('03Data'[COUNTRY]), ALL('03Data'[COUNTRY : LOT]), '03Data'[EXPORT_FRUIT] = _SelectedFruit),
    _IsSelectedFuit && _IsSelectedCountry, CALCULATE([SUMVAL_FC], ALL('03Data'[COUNTRY]), ALL('03Data'[COUNTRY : LOT]),'03Data'[COUNTRY] = _SelectedCountry, '03Data'[EXPORT_FRUIT] = _SelectedFruit),
    NOT(_IsSelectedFuit) && _IsSelectedCountry, CALCULATE([SUMVAL_FC], ALL('03Data'),ALL('03Data'[COUNTRY : LOT]),'03Data'[COUNTRY] = _SelectedCountry),
    CALCULATE([SUMVAL_FC], ALL('03Data'[COUNTRY : LOT]), ALL('03Data'[EXPORT_FRUIT]))) 


VAR _Result = 
IF(_Value>=0, [_Rank2],[_Rank1])

RETURN
_Result

     

     

     

     

    you can filter our in the graph the 0 values that are not useful

    If it answers your question, please mark my reply as the solution. Thanks

     

    • newyearrrr's avatar
      newyearrrr
      Frequent Visitor
      2 years ago

      thats work ! Thank you for your help.