Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Issue with filtering

Hi all, I have some question in decile filtering.
In my matrix table I have "# of customers" column which total is divided deciles(01, 02,..., 10). And each deciles equals each others

DecileSet = 
IF( Query1[YearDecile] <= Query1[OnePart],"Decile01",
if(Query1[YearDecile]>Query1[OnePart] && Query1[YearDecile]<=(2*Query1[OnePart]),"Decile02",
if(Query1[YearDecile]>2*Query1[OnePart] && Query1[YearDecile]<=(3*Query1[OnePart]),"Decile03",
if(Query1[YearDecile]>3*Query1[OnePart] && Query1[YearDecile]<=(4*Query1[OnePart]),"Decile04",
if(Query1[YearDecile]>4*Query1[OnePart] && Query1[YearDecile]<=(5*Query1[OnePart]),"Decile05",
if(Query1[YearDecile]>5*Query1[OnePart] && Query1[YearDecile]<=(6*Query1[OnePart]),"Decile06",
if(Query1[YearDecile]>6*Query1[OnePart] && Query1[YearDecile]<=(7*Query1[OnePart]),"Decile07",
if(Query1[YearDecile]>7*Query1[OnePart] && Query1[YearDecile]<=(8*Query1[OnePart]),"Decile08",
if(Query1[YearDecile]>8*Query1[OnePart] && Query1[YearDecile]<=(9*Query1[OnePart]),"Decile09",
if(Query1[YearDecile]>9*Query1[OnePart] && Query1[YearDecile]<=(10*Query1[OnePart]),"Decile10",""
)
)))))))))
 
OnePart = Query1[YearDecileMax]/10
 
YearDecileMax =
CALCULATE (
MAX ( Query1[YearDecile] ),
FILTER ( Query1, Query1[Fiscal Year] = EARLIER ( Query1[Fiscal Year] ) )
)
 
Memb = CALCULATE(SUM(Query1[isMember])) / CALCULATE(SUM(Query1[isMember]), ALLEXCEPT(Query1, Query1[DecileSet]))
 
 
 
When I filtering by year everything is working good (total change value and deciles(01,02,..,10) change too).
 
But when I filter by month or by customer the deciles(01,02,...,10) does not equal each others.
 
 
 
Please help me figure this critical problem. When I am filtering by all filters (year, month or costumer) I want to see equals values on each deciles :)
Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous ,

     

    I'd like to suggest you share your pbix file with some sample data for test, it will help us to clarify your scenario.

     

    In addition, I found your formula are based on year value. When you filter on month, it will filter table records return multiple years who matched with current selection.

     

    If that is a case, your formula will get wrong result.(calculation is based on single year, you are try to use it on multiple years)

     

    Regards,

    Xiaoxin Sheng