Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Comparison measure not working when using Sort By on a column

Hi everyone! I am fairly new to PBI and one of my comparison formula stopped working recently. Scratched my head a few hours before realizing where the issue came from.   I work in a retail organi...
Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    Hi Anonymous 

     

    This is related with the way that the filtering of a column is calculated when you use the CALCULATE, the use of the expression of a table is like you are using the ALL statetment, in this case when you place the value of the version it gets incorrect.

     

    Try the following code:

    USD Cst1 vs LY Act % V2 = 
VAR PREVYR = SELECTEDVALUE('Calendar'[Year])-1
VAR BASELINE = CALCULATE([USD Cst1], 'Calendar'[Year]=PREVYR,'Map_Version'[Version]="ACT", ALLSELECTED(Map_Version))
    RETURN
        DIVIDE([USD Cst1]-BASELINE,BASELINE,0)

     

     

     

    Has you can see the result is the same even when you change the order:

     

MFelix's avatar
MFelix
Icon for Super User rankSuper User
4 years ago

Hi Anonymous 

 

This is related with the way that the filtering of a column is calculated when you use the CALCULATE, the use of the expression of a table is like you are using the ALL statetment, in this case when you place the value of the version it gets incorrect.

 

Try the following code:

USD Cst1 vs LY Act % V2 = 
VAR PREVYR = SELECTEDVALUE('Calendar'[Year])-1
VAR BASELINE = CALCULATE([USD Cst1], 'Calendar'[Year]=PREVYR,'Map_Version'[Version]="ACT", ALLSELECTED(Map_Version))
    RETURN
        DIVIDE([USD Cst1]-BASELINE,BASELINE,0)

 

 

 

Has you can see the result is the same even when you change the order:

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

It is WORKING 🙂 An ENORMOUS Than you for your help - very much appreciated!