Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

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]
 
bntlvsr_0-1653617127303.png bntlvsr_2-1653617231349.png

 

 

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

bntlvsr_3-1653617540758.png

 

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

bntlvsr_4-1653617678970.png

 

 

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

Thanks in advance~

1 ACCEPTED SOLUTION

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)

 

MFelix_0-1653986974508.png

 

 

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
MFelix
Super User
Super User

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.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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

bntlvsr_0-1653976309222.png

 

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

bntlvsr_1-1653976436790.png

 

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)

 

MFelix_0-1653986974508.png

 

 

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors