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 ,

 

Since I don't have access to the data cannot pin point the issue exactly, first try to change your code to :

 

USD Cst1 vs LY Act % =
VAR PREVYR =
    DATEADD ( 'Calendar'[Dates], -1, YEAR )
VAR BASELINE =
    CALCULATE ( [USD Cst1], PREVYR, 'Map_Version'[Version] = "ACT" )
RETURN
    DIVIDE ( [USD Cst1] - BASELINE, BASELINE, 0 )

 

Also believe that one error is the way you are picking up the Version.

 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi MFelix and thanks a lot for offering to help!

 

Belowis a sample pbix:

https://drive.google.com/file/d/1iu8KKYyTQ4cjFJGdbRsErbv6Ms2QglEV/view?usp=sharing

 

Please note: this powerbi used to provide various analysts acros the organization with a convenient "Analyze in excel" tool through excl pivots connecting to this dataset,

as such:

a/ the calendar is custom, not a date table

b/ the values are measures which are the sum of base data (e.g. [usd cst1] is defined as the sum of [usd cst1 (raw)] values).

=> As you probably know, if I did not do this the data could not be used in "Analyze in excel" easily.

In any case I believe this is not what is causing the issue with the version?

 

Thanks a lot in advance - let me know if anything is not clear?

 

As per the below:

 

1. Leaving 'Map_Version'[Version] as is (alphabetical sort, with "Actual" in first place) => no problem

 

2. Sorting 'Map_Version'[Version] by column 'Map_Version'[Version order] (so as to display Actuals on the right of the table) => the %age variation disappear

 

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