Forum Discussion
Percentilex.inc/exc issue
Hi , king4646
Thanks for your quick response !
Sorry for i have no excel developer experience and for your issue is "when I use my slicer, I filter by a field called EXCLUDED CUSTOMER Y_N. I select the "no" to only bring in customers that are not excluded and at that point the percentiles calculated seem to break."
I'm testing this here and I'm sorry, this really doesn't support Joan Slicer. Maybe it's a matter of context!And for this , can you try to use this dax code to test , i modify the ALLSELECTED() function.You can try to check if this can meet your need:(This is put for the Customer table visual)
20th percentile-2 = var _20= PERCENTILEX.INC(ADDCOLUMNS(CALCULATETABLE( SUMMARIZE('Published','Published'[Customer_Media_Type],'Published'[Customer]) ,ALL(Published[Customer_Media_Type],'Published'[Customer])) ,"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 0.2) Return _2080th percentile-2 = var _80= PERCENTILEX.INC(ADDCOLUMNS(CALCULATETABLE( SUMMARIZE('Published','Published'[Customer_Media_Type],'Published'[Customer]) ,ALL(Published[Customer_Media_Type],'Published'[Customer])),"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 0.8) Return _80Dynamic Cust Pts = var _20= PERCENTILEX.INC(ADDCOLUMNS(CALCULATETABLE( SUMMARIZE('Published','Published'[Customer_Media_Type],'Published'[Customer]) ,ALL(Published[Customer_Media_Type],'Published'[Customer])),"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 0.2) var _40 = PERCENTILEX.INC(ADDCOLUMNS( CALCULATETABLE( SUMMARIZE('Published','Published'[Customer_Media_Type],'Published'[Customer]) ,ALL(Published[Customer_Media_Type],'Published'[Customer])) ,"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 0.4) var _60 = PERCENTILEX.INC(ADDCOLUMNS( CALCULATETABLE( SUMMARIZE('Published','Published'[Customer_Media_Type],'Published'[Customer]) ,ALL(Published[Customer_Media_Type],'Published'[Customer])),"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 0.6) var _80 = PERCENTILEX.INC(ADDCOLUMNS(CALCULATETABLE( SUMMARIZE('Published','Published'[Customer_Media_Type],'Published'[Customer]) ,ALL(Published[Customer_Media_Type],'Published'[Customer])) ,"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 0.8) var _90 = PERCENTILEX.INC(ADDCOLUMNS( CALCULATETABLE( SUMMARIZE('Published','Published'[Customer_Media_Type],'Published'[Customer]) ,ALL(Published[Customer_Media_Type],'Published'[Customer])) ,"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 0.9) var _100 = PERCENTILEX.INC(ADDCOLUMNS( CALCULATETABLE( SUMMARIZE('Published','Published'[Customer_Media_Type],'Published'[Customer]) ,ALL(Published[Customer_Media_Type],'Published'[Customer])) ,"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 1) var _revenue = SUM(Published[Revenue]) return IF( _revenue < _20 ,1, IF(_revenue >= _20 &&_revenue<_40 ,2 ,IF( _revenue >=_40 && _revenue<_60 ,3, IF(_revenue>=_60 && _revenue < _80 , 4 , IF(_revenue>=_80 && _revenue < _90 ,5 , 6)))))The result is as follows:
we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thank you for your reply.
Unfortunately on excel 365 I'm getting the error "this formula is invalid or incomplete: 'too many arguments were passed to the ALLSELECTED function. The maximum argument count
I have removed the customer media type field from my pivot table
For original measure you created for industry type works fine when changing "Industry" to "Customer". When my pivot is all customers and revenue it is working as expected.
The issue is when I use my slicer, I filter by a field called EXCLUDED CUSTOMER Y_N. I select the "no" to only bring in customers that are not excluded and at that point the percentiles calculated seem to break. It know what 100% is but returns blank right up to 80th percentile and then a significantly low number is pulling through as 80th percentile causing all customers to score 4-6
Community SupportHi , king4646
Thanks for your quick response !
Sorry for i have no excel developer experience and for your issue is "when I use my slicer, I filter by a field called EXCLUDED CUSTOMER Y_N. I select the "no" to only bring in customers that are not excluded and at that point the percentiles calculated seem to break."
I'm testing this here and I'm sorry, this really doesn't support Joan Slicer. Maybe it's a matter of context!
And for this , can you try to use this dax code to test , i modify the ALLSELECTED() function.You can try to check if this can meet your need:(This is put for the Customer table visual)
20th percentile-2 = var _20= PERCENTILEX.INC(ADDCOLUMNS(CALCULATETABLE( SUMMARIZE('Published','Published'[Customer_Media_Type],'Published'[Customer]) ,ALL(Published[Customer_Media_Type],'Published'[Customer])) ,"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 0.2)
Return
_20
80th percentile-2 = var _80= PERCENTILEX.INC(ADDCOLUMNS(CALCULATETABLE( SUMMARIZE('Published','Published'[Customer_Media_Type],'Published'[Customer]) ,ALL(Published[Customer_Media_Type],'Published'[Customer])),"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 0.8)
Return
_80
Dynamic Cust Pts = var _20= PERCENTILEX.INC(ADDCOLUMNS(CALCULATETABLE( SUMMARIZE('Published','Published'[Customer_Media_Type],'Published'[Customer]) ,ALL(Published[Customer_Media_Type],'Published'[Customer])),"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 0.2)
var _40 = PERCENTILEX.INC(ADDCOLUMNS( CALCULATETABLE( SUMMARIZE('Published','Published'[Customer_Media_Type],'Published'[Customer]) ,ALL(Published[Customer_Media_Type],'Published'[Customer])) ,"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 0.4)
var _60 = PERCENTILEX.INC(ADDCOLUMNS( CALCULATETABLE( SUMMARIZE('Published','Published'[Customer_Media_Type],'Published'[Customer]) ,ALL(Published[Customer_Media_Type],'Published'[Customer])),"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 0.6)
var _80 = PERCENTILEX.INC(ADDCOLUMNS(CALCULATETABLE( SUMMARIZE('Published','Published'[Customer_Media_Type],'Published'[Customer]) ,ALL(Published[Customer_Media_Type],'Published'[Customer])) ,"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 0.8)
var _90 = PERCENTILEX.INC(ADDCOLUMNS( CALCULATETABLE( SUMMARIZE('Published','Published'[Customer_Media_Type],'Published'[Customer]) ,ALL(Published[Customer_Media_Type],'Published'[Customer])) ,"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 0.9)
var _100 = PERCENTILEX.INC(ADDCOLUMNS( CALCULATETABLE( SUMMARIZE('Published','Published'[Customer_Media_Type],'Published'[Customer]) ,ALL(Published[Customer_Media_Type],'Published'[Customer])) ,"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 1)
var _revenue = SUM(Published[Revenue])
return
IF( _revenue < _20 ,1, IF(_revenue >= _20 &&_revenue<_40 ,2 ,IF( _revenue >=_40 && _revenue<_60 ,3, IF(_revenue>=_60 && _revenue < _80 , 4 , IF(_revenue>=_80 && _revenue < _90 ,5 , 6)))))
The result is as follows:
we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
You are a genius, I love you 😂