Forum Discussion
Comparison measure not working when using Sort By on a column
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:
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
Super UserHi 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:
It is WORKING 🙂 An ENORMOUS Than you for your help - very much appreciated!