cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

## Dynamic percentile of pivot revenue measure

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?

1 ACCEPTED SOLUTION
Community Support

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

6 REPLIES 6
Community Support

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

Frequent Visitor

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=11...

Community Support

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

Frequent Visitor

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

Frequent Visitor

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

Frequent Visitor

can any pros help @Greg_Deckler @tamerj1 @FreemanZ @johnt75

## Helpful resources

Announcements

#### Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors