Field<>BLANK() Doesn't Work after Sorting the Field by Another Column

[DearestYoki]

Hi all,

I encountered an issue while changing the sorting order of axis & legend.

The measure I created is [%SOM] (i.e. share of market) and the expression is as follow:

IF(SELECTEDVALUE(Filter1[Period1])="L6M",

CALCULATE(SUMX(FILTER(Raw1,Raw1[Level]="Item"),Raw1[Volume/Value]),DATESBETWEEN('Calendar'[Date],[L6M Start],[PeriodEnd]))/

CALCULATE(SUMX(FILTER(Raw1,Raw1[Level]="Item"),Raw1[Volume/Value]),DATESBETWEEN('Calendar'[Date],[L6M Start],[PeriodEnd]),MASTER_PRODUCT[Corp.]<>BLANK()),

The full expression is very long with multiple if statements for different periods. Now I am only showing a portion of it. After applying Master_Product[Corp.] as axis, the expression is basically calculationg volume/value of individual corp divided by volume/value for all corps. The reason why I didn't use ALL(corp.) is I don't want to take rows with empty corp. but valid volume/value into account.

The chart will look like this:

In this chart, all percentages sum up equals to 100%, which validates the expression above. However, the axis is in alphabetical order. I have created sorting index column for each Corp. in the same table using power query editor.

However, after I apply "Sort by Column", the order of Corp. appears correctly but the measure becomes wrong. All [%SOM] give me 100% as the result.

I think after applying sort by column on Corp., the filter Corp. <> BLANK() no longer works properly. I have done some research about this and found similar issue posted before regarding ALL() function, but the fix for that one doesn't work for me.

It would be great if any of you can help me with this. Thanks so much!

Best Regards,

Yoki

[amitchandak · Accepted solution]

@DearestYoki, See if this formula can help

IF(SELECTEDVALUE(Filter1[Period1])-"L6M",
CALCULATE(SUMX(FILTER(Raw1,Raw1[Level]-"Item"),Raw1[Volume/Value]),DATESBETWEEN('Calendar'[Date],[L6M Start],[PeriodEnd]))/
CALCULATE(SUMX(FILTER(Raw1,Raw1[Level]-"Item"),Raw1[Volume/Value]),DATESBETWEEN('Calendar'[Date],[L6M Start],[PeriodEnd]),
filter(allselected(MASTER_PRODUCT),MASTER_PRODUCT[Corp.]<>BLANK())))

[DearestYoki]

Thanks! Your solution works perfectly 🙂