Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Sorting by Column Corrupting Matrix

Hi guys,

 

I've got a customer sort order of a variable Reorder Bucket, the columns on a few of my matrices. When I apply the sort to the column, the matrices become corrupted - only the first column will get a correct value. 

 

This is the simplest example of a measure used in the matrices:

 

Retention Buckets =
Divide(COUNTA(Orders[Clarity Order ID]),
CALCULATE(counta(Orders[Clarity Order ID]),Orders[Reorder Bucket]="T"),
 
Before the sort, all columns had values. 
After the sort, only column T had values.
 
Any idea why the sort is impacting the measure?
 
Thanks
Oli
Need Help

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      Thanks Liang! I think this will solve it, I will try when I get home and update here.

      Oli
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This worked, thanks again!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi V-lianl-msft,

       

      I've managed to use this solution for most of my graphs but on one, I cannot get it to work. Whatever I try, it just ends up with a failed graph and the error "Can't display the visual. There's not enough memory to complete this operation."

       

      Do you have any idea what I can try? Below is the measure that's failing when the other measure "Retention Buckets" is amended to filter on both master and sort columns.

       

      Cumulative Retention Quarterly = CALCULATE([Retention Buckets]*AVERAGE(Orders[Total Amount]),FILTER(ALLEXCEPT(Orders,Orders[Region],Orders[Trial (month/year)],Orders[Trial quarter]),Orders[Reorder Bucket Priority]<=MAX(Orders[Reorder Bucket Priority])),Orders[Reorder Bucket Priority]>0)
       
      Here are the new and old definitions of Retention Buckets: 
       
      Retention Buckets (NEW) =
      Divide(COUNTA(Orders[Clarity Order ID]),
      CALCULATE(counta(Orders[Clarity Order ID]),Orders[Reorder Bucket]="T",Orders[Reorder Bucket Priority]=1)
       
      Retention Buckets (OLD) =
      Divide(COUNTA(Orders[Clarity Order ID]),
      CALCULATE(counta(Orders[Clarity Order ID]),Orders[Reorder Bucket]="T")
       
      Thanks
      Oli
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      Hi parry2k,

      Thanks for your reply, I will do so if Liang's advice doesn't work when I'm back at my PC. It seems to be the exact same issue though so hopefully it does.

      Thanks
      Oli