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
vrajkumar1
Helper II
Helper II

REMOVEFILTERS (or ALL) does not seem to work if the column it is used on is already sorted

REMOVEFILTERS (or ALL) does not seem to work if the column it is used on is already sorted based on another column (I used SORT BY COLUMN in the Data pane on desktop).

 

This is the measure I am using in a matrix visual

Pct_Curr = DIVIDE(SUM(MONTHLY_VOLUME_INSIGHTS_PayorMixShift[CURR_CHGS]), CALCULATE(SUM(MONTHLY_VOLUME_INSIGHTS_PayorMixShift[CURR_CHGS]), REMOVEFILTERS(MONTHLY_VOLUME_INSIGHTS_PayorMixShift[GroupedProduct3])),0)

 

My data is as below

 

GroupedProduct3CURR_CHGS
C              69,852,012
Mc           762,602,711
Mcd           623,861,038
Md Cm           803,486,757
Md Cr           945,478,482
Other              91,728,977

 

I am dividing each row by the column total to get percent of column total for each GroupedProduct3.  GroupedProduct3 is sorted based on another column.  This sorting assigns a number to each GroupedProduct3 based on CURR_CHGS

If I sort GroupedProduct3 using SORT BY COLUMN in the Data pane, my measure results in 100% for each row which is inaccurate (its almost as though REMOVE FILTERS is not applied).  But if I don’t sort this column then the numbers are what I am expecting.

The reason I want this in this way is to show GroupedProduct3 sorted by CURR_CHGS column (not the measure result)

How can I get this to work?

 

1 ACCEPTED SOLUTION
vrajkumar1
Helper II
Helper II

I have figured out the solution to this by using the solution offered in 

"Unable to sort column using "Sort by column" when a filter condition is applied in measure"

I added the sorter column also in my REMOVE FILTERS

Before:

REMOVEFILTERS(MONTHLY_VOLUME_INSIGHTS_PayorMixShift[GroupedProduct3])

After

REMOVEFILTERS(MONTHLY_VOLUME_INSIGHTS_PayorMixShift[GroupedProduct3],MONTHLY_VOLUME_INSIGHTS_PayorMixShift[Product3_SortOrder])

View solution in original post

1 REPLY 1
vrajkumar1
Helper II
Helper II

I have figured out the solution to this by using the solution offered in 

"Unable to sort column using "Sort by column" when a filter condition is applied in measure"

I added the sorter column also in my REMOVE FILTERS

Before:

REMOVEFILTERS(MONTHLY_VOLUME_INSIGHTS_PayorMixShift[GroupedProduct3])

After

REMOVEFILTERS(MONTHLY_VOLUME_INSIGHTS_PayorMixShift[GroupedProduct3],MONTHLY_VOLUME_INSIGHTS_PayorMixShift[Product3_SortOrder])

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.