topic Re: Dynamic percentile of pivot revenue measure in DAX Commands and Tips

Dynamic percentile of pivot revenue measure

king4646 — Thu, 25 May 2023 19:31:53 GMT

Hello, I have a dataset that I isolate by industry types and customers. Operating in excel office 365

I'm looking at recreating industy revenue points dynamically based on what is in the pivot table so that if I filter by month or quarter etc the percentiles change within the measure. Currently I have taken all values of revenue manually and plugged them into a measure manually as shown in the image. Scoring 1 if under 20% percentile, 2 if between 24816 and 47598 etc up to 6 point. I want to then translate that across to customers instead of industries

is this possible?

Re: Dynamic percentile of pivot revenue measure

king4646 — Fri, 26 May 2023 21:11:37 GMT

can any pros help Greg_Deckler tamerj1 FreemanZ johnt75

Re: Dynamic percentile of pivot revenue measure

v-yueyunzh-msft — Mon, 29 May 2023 03:52:17 GMT

Hi , king4646

According to your description, you want to "Dynamic percentile of pivot revenue measure".

Here are the steps you can refer to :
(1)This is my test data:

(2)We can create a measrue like this:

Measure = var _20= PERCENTILEX.INC(ADDCOLUMNS( ALLSELECTED('Table'[Industry]) ,"Sum of Revenue" , CALCULATE(SUM('Table'[Revenue]))) , [Sum of Revenue] , 0.2) var _40 = PERCENTILEX.INC(ADDCOLUMNS( ALLSELECTED('Table'[Industry]) ,"Sum of Revenue" , CALCULATE(SUM('Table'[Revenue]))) , [Sum of Revenue] , 0.4) var _60 = PERCENTILEX.INC(ADDCOLUMNS( ALLSELECTED('Table'[Industry]) ,"Sum of Revenue" , CALCULATE(SUM('Table'[Revenue]))) , [Sum of Revenue] , 0.6) var _80 = PERCENTILEX.INC(ADDCOLUMNS( ALLSELECTED('Table'[Industry]) ,"Sum of Revenue" , CALCULATE(SUM('Table'[Revenue]))) , [Sum of Revenue] , 0.8) var _90 = PERCENTILEX.INC(ADDCOLUMNS( ALLSELECTED('Table'[Industry]) ,"Sum of Revenue" , CALCULATE(SUM('Table'[Revenue]))) , [Sum of Revenue] , 0.9) var _100 = PERCENTILEX.INC(ADDCOLUMNS( ALLSELECTED('Table'[Industry]) ,"Sum of Revenue" , CALCULATE(SUM('Table'[Revenue]))) , [Sum of Revenue] , 1) var _revenue = SUM('Table'[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)))))

(3)Then we can put it on the visual , the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that 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

Re: Dynamic percentile of pivot revenue measure

king4646 — Thu, 08 Jun 2023 15:35:15 GMT

Sorry for the delay I was on holiday. Thank you very much! It worked perfectly in that situation however when I try to adapt to a customer level it hasn't worked. Could you please assist further?

my sample excel file is on the link below

https://docs.google.com/spreadsheets/d/1qJ1FQmAeGcc_Wh6dK1_cRsiDW2NE000R/edit?usp=drive_link&ouid=118435087330547460302&rtpof=true&sd=true

Re: Dynamic percentile of pivot revenue measure

v-yueyunzh-msft — Fri, 09 Jun 2023 07:28:55 GMT

Hi , king4646

Thanks for your response!According to your description, when you adapt to a customer level it hasn't worked.

And i try to access your url , it return 401 and i have no access to it .

For this , you can try to replace all the "'Table'[Industry]" to 'Table'[customer] if it can help?

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

Re: Dynamic percentile of pivot revenue measure

king4646 — Fri, 16 Jun 2023 10:50:40 GMT

Hi, I have finally had chance to replicate in BI and show. In the dynamic industry type the points go from 1-6 however that its returning 4-6. I have put in measures that return the 20th and 80th percentile to show the difference when looking at industry vs customer

I've tried about 10 different ways but I think I am too inexperienced with this type of nuance

PowerBI

I can't attach my PBIX file so above is a onedrive link that I have shared access to

Re: Dynamic percentile of pivot revenue measure

king4646 — Tue, 20 Jun 2023 11:34:18 GMT

v-yueyunzh-msft please are you able to offer any further support?