Comparison measure not working when using Sort By on a column

[bntlvsr]

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 organization, and I follow our sales on PBI.

I have the following tables:

1. "Consolidated Sales" table, with my sales data (column named [USD cst1]), organized:
   • by location (store)
   • by date
   • by version (Budget "BUD", Actual "ACT", Revised 1 "R1", Revised 2 "R2" and commercial target "CT")
2. A Date mapping table "Calendar"
3. A Version mapping table "Map_Table" (for Budget, Actual, R1, R2, CT)

I calculate a measure to get this year's sales (no matter the version) versus last year ACTUAL sales ("Actual" version only), as below:

USD Cst1 vs LY Act % =

VAR PREVYR = SELECTEDVALUE('Calendar'[Year])-1

VAR BASELINE = CALCULATE([USD Cst1], 'Calendar'[Year]=PREVYR,'Map_Version'[Version]="ACT")

RETURN

DIVIDE([USD Cst1]-BASELINE,BASELINE,0)

Now, this was working perfectly fine until I needed my versions (Actual, Budget etc.) to be sorted in columns with the ACTUAL on the right hand side.

So, I created a new column in the "Map_Version" table, called [Version_Order] as below, and use the "Sort by" function on column [Version]

 

Since doing this

• The versions in my table are correctly ordered ("Actual" on the right hand side of each year)
• BUT the formula no longer works - see below

 1. Before: "% vs LY" works fine, but Actual data "ACT" is on the left -> must be on the right

2. After: Actual data "ACT" is on the right OK, but measure "% vs LY" no longer works

Is this a known issue and is there a workaround? I cannot find any topic on this subject!

Thanks in advance~

[MFelix]

Hi @bntlvsr ,

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.

[bntlvsr]

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

Hi @bntlvsr 

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:

[bntlvsr]

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