Sort By affecting Measure Results

[Andshepch]

Hi,

I have a problem where I have a measure that works fine in a table, but when I apply a Sort By in the Data View the measure stops working.

I have the following 4 measures

SBU Win Rate = 
SUM ( 'SBU Sold inc Totals'[Sold CV €M] )
    / (
        SUM ( 'SBU Sold inc Totals'[Sold CV €M] )
            + SUM ( 'SBU LDQ inc Totals'[LDQ CV €M] )
    )

SBU Rolling 12 Month Win Rate = 
CALCULATE (
    [SBU Win Rate],
    '4 Calendar'[Date]
        >= DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), 1 )
        && '4 Calendar'[Date] < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
)

SBU Rolling WinRate GBL CIS = 
CALCULATE (
    [SBU Rolling 12 Month Win Rate],
    'BU Mapping'[Summary Group Indent] = "GBL CIS")

SBU Rolling 12 Win Rate vs GBL CIS = 
DIVIDE (
    [SBU Rolling 12 Month Win Rate],
    [SBU Rolling WinRate GBL CIS],
    BLANK ()
)

The desired outcome is to divide each value of the 12 month rolling win rate for Summary Group Indent, by the value where Summary Group Indent = "GBL CIS" i.e. to compare all other win rates against GBL CIS. (compare SBU Rolling 12 Month Win Rate with SBU Rolling WinRate GBL CIS) - this works fine as per the below table - but I need to sort the Summary Group Indent by a sorting index which is in a related table "BU Mapping" using Sort by Column in the Data view - the minute I do this, my results disappear, except for the numbers on the GBL CIS line. I have found similar problems to this and it looks liek I should be using All() to fix it, but I'm afraid I can't work out how, so any help would be greatly appreciated - thanks!

Summary Group Indent	SBU Win Rate	SBU Rolling 12 Month Win Rate	SBU Rolling WinRate GBL CIS	SBU Rolling 12 Win Rate vs GBL CIS
Double Count	100.00%	100.00%	63.02%	1.59
Elimination	100.00%	100.00%	63.02%	1.59
-Germany	57.48%	86.12%	63.02%	1.37
-Insurance	80.00%	85.27%	63.02%	1.35
-France	72.20%	79.51%	63.02%	1.26
-CE	60.83%	73.70%	63.02%	1.17
SBU FS	56.78%	67.59%	63.02%	1.07
-North America	55.14%	66.25%	63.02%	1.05
SBU Americas & APAC	55.38%	65.49%	63.02%	1.04
SBU Europe	59.08%	63.10%	63.02%	1.00
GBL CIS	56.79%	63.02%	63.02%	1.00
-UK	56.39%	60.77%	63.02%	0.96
-Netherlands	60.65%	58.30%	63.02%	0.93
-Banking	39.51%	56.67%	63.02%	0.90
-APAC	59.51%	55.08%	63.02%	0.87
-Scandinavia	57.18%	40.30%	63.02%	0.64
-Spain	51.13%	39.30%	63.02%	0.62

[Andshepch]

Perfect! Thank you.

[v-yiruan-msft]

Hi @Andshepch ,

Please review the solution in the following threads, hope they can help resolve the problem.

Sort By Column changes the Measure results

Side effects of the Sort By Column setting in DAX

If the above solutions not working for you, please provide some sample table in tables which refers in 4 measures and desired result with examples. It is better if you can share your sample pbix file in order to provide a suitable solution. Thank you.

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[Andshepch]

Hi there,

Thank you so much for your reply, I had read both of those articles and still could not fix my problem. I have attached a link to a sample PBIX file which demonstrates what I am trying to do. Any pointers you can give me woudl be greatly appreciated.

https://www.dropbox.com/s/y78hrwpdrwzr6nk/Test%20Rolling%2012.pbix?dl=0 

Thanks!

Andrew

[v-yiruan-msft · Accepted solution]

Hi @Andshepch ,

I made the below updates in your sample pbix file, please check whether that is what you want:

1. Select the field [Summary Group Indent] of table BU Mapping and sort by field [BU Order]

2. Update the formula of measure [SBU Rolling WinRate GBL CIS] as below

SBU Rolling WinRate GBL CIS = 
CALCULATE (
    [SBU Rolling 12 Month Win Rate], 
      FILTER(ALL('BU Mapping'),'BU Mapping'[Summary Group Indent] = "GBL CIS"))

Best Regards