Issue with filtering

[Anonymous]

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 🙂

[Anonymous]

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