Forum Discussion

v-nchawla's avatar
v-nchawla
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

Urgent Help

Hello Everyone,

 

I have a table wherein I am making some dynamic calculations using whatif parameters, below is the code:-

 

Revenue Top Seats Top = var a = ADDCOLUMNS(SUMMARIZE(Main,Main[TPID]),"test",[Rev Potential],"test 2",[Seats Potential.]) var Quartile_1 = PERCENTILEX.INC(a,[test],0.25) var Quartile_2 = PERCENTILEX.INC(a,[test],0.75) Var Quartile_4 = PERCENTILEX.INC(a,[test],1) var SeatsQtr1 = PERCENTILEX.INC(a,[test 2],0.25) var seatsqtr2 = PERCENTILEX.INC(a,[test 2],0.75) var seatqtr3 = PERCENTILEX.INC(a,[Test 2],1)var Final = ADDCOLUMNS(a,"Quartile",IF([test]<=Quartile_1,"Bottom Quartile",IF([test]>Quartile_1&&[test]<=Quartile_2,"Middle Quartile",IF([test]>Quartile_2,"Top Quartile"))),"Quartile 2",IF([test 2]<=SeatsQtr1,"Bottom Quartile",IF([test 2]>SeatsQtr1&&[test 2]<=seatsqtr2,"Middle Quartile",IF([test 2]>seatsqtr2,"Top Quartile")))) var last = ADDCOLUMNS(Final,"Revenue Top Seats Bottom",IF([Quartile]="Top Quartile"&&[Quartile 2]="Top Quartile",1,0)) return SUMX(FILTER(last,[Revenue Top Seats Bottom]=1),[Revenue Top Seats Bottom])
 
This code is absolutely running fine, however, when I select any filter the percentiles (Quartile 1...2,Seats Qtr1...Seats Qtr2) all of these quartile variables are also adjusted when I filter my TPID, however, I want to actually make my percentiles static, i.e., even though I select any industry from the slicer, the percentile should always be calculated on the entire column, I have tried usng all and everything but I am stuck here.
 
Can anyone help me calculating thiese percentiles on the entire column by ignoring the outside filters? Remember only percentiles, rest calculation is running smotthly.
 
Thanks.
Need Help

4 Replies