could you help me dynamic ranking data with measure

[newyearrrr]

------- > 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

[Alex87 · Accepted solution]

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

[Alex87 · Accepted solution]

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 · Accepted solution]

thats work ! Thank you for your help.