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

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:

     

4 Replies

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