Percentilex.inc/exc issue

[king4646]

Hi Guys, I'm having an absolutely huge headache from a measure one of the guys here created for my excel.

It seems to work absolutely fine when I am looking at industry level it scores 1-6 appropriately, at customer level it is broken and only scores 4-6 so the percentile readings must be off in the way it's interpreting the revenue on the pivot table

I recreated both measures in BI to test and from both a customer and industry point of view the formula works! Im lost anyone able to offer support or context on how to get it working in excel 365?

Summary

- Formula working in BI for both customer and industry

- Formula working in excel pivot for industry

- Formula not calculating the percentiles correctly on customer measure in excel and therefore scoring customers 4-6 instead of 1-6

- on the sample file it's scoring everybody 6

The formula is

=VAR _20 = PERCENTILEX.INC(ADDCOLUMNS(ALLSELECTED(Published[Customer]),"sum of revenue", CALCULATE(SUM(Published[Revenue]))),[sum of revenue],0.2) VAR _40 = PERCENTILEX.INC(ADDCOLUMNS(ALLSELECTED(Published[Customer]),"sum of revenue", CALCULATE(SUM(Published[Revenue]))),[sum of revenue],0.4) VAR _60 = PERCENTILEX.INC(ADDCOLUMNS(ALLSELECTED(Published[Customer]),"sum of revenue", CALCULATE(SUM(Published[Revenue]))),[sum of revenue],0.6) VAR _80 = PERCENTILEX.INC(ADDCOLUMNS(ALLSELECTED(Published[Customer]),"sum of revenue", CALCULATE(SUM(Published[Revenue]))),[sum of revenue],0.8) VAR _90 = PERCENTILEX.INC(ADDCOLUMNS(ALLSELECTeD(Published[Customer]),"sum of revenue", CALCULATE(SUM(Published[Revenue]))),[sum of revenue],0.9) VAR _100 =PERCENTILEX.INC(ADDCOLUMNS(ALLSELECTED(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)))))

Link to the sample file

https://1drv.ms/x/s!AiUg-AKQ3-onnGyeZjZF9yQ0K8LS

[king4646]

To add further insight to this. I've got a slicer that excludes certain customers, when I deselect and allow all customers to be looked at the formula works so it is have trouble with a filter being applied to the pivot table and then it would seem I am either using ALLSELECTED wrong or there's a misfire on the ALLSELECTED function in this context

[v-yueyunzh-msft]

Hi , @king4646 

This seems to have replied to you before, sorry, this is still a problem for you.If your last problem is solved, I will probably miss your reply, because our Dashboard will not remind you, you can create a new case like this, which will assign you the corresponding engineer to help you solve the problem .

I check it in the pbix .We can clearly see that there are two dimension fields placed in your Customer visual.

So in order to return the correct value, we need to add these two dimension fields to our measure.

20th percentile = var _20= PERCENTILEX.INC(ADDCOLUMNS( ALLSELECTED(Published[Customer],'Published'[Customer_Media_Type]) ,"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 0.2)
Return
_20

80th percentile = var _80= PERCENTILEX.INC(ADDCOLUMNS( ALLSELECTED(Published[Customer],Published[Customer_Media_Type]) ,"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 0.8)
Return
_80

Dynamic Cust Pts = var _20= PERCENTILEX.INC(ADDCOLUMNS( ALLSELECTED(Published[Customer],Published[Customer_Media_Type]) ,"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 0.2)
var _40 = PERCENTILEX.INC(ADDCOLUMNS( ALLSELECTED(Published[Customer],Published[Customer_Media_Type]) ,"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 0.4)
var _60 = PERCENTILEX.INC(ADDCOLUMNS( ALLSELECTED(Published[Customer],Published[Customer_Media_Type]) ,"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 0.6)
var _80 = PERCENTILEX.INC(ADDCOLUMNS( ALLSELECTED(Published[Customer],Published[Customer_Media_Type]) ,"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 0.8)
var _90 = PERCENTILEX.INC(ADDCOLUMNS( ALLSELECTED(Published[Customer],Published[Customer_Media_Type]) ,"Sum of Revenue" , CALCULATE(SUM(Published[Revenue]))) , [Sum of Revenue] , 0.9)
var _100 = PERCENTILEX.INC(ADDCOLUMNS( ALLSELECTED(Published[Customer],Published[Customer_Media_Type]) ,"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)))))

Then we can get this:

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

[king4646]

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

[v-yueyunzh-msft · Accepted solution]

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
_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

[king4646]

You are a genius, I love you 😂