Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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?
Solved! Go to Solution.
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
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
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
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
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
I can't attach my PBIX file so above is a onedrive link that I have shared access to
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
32 | |
15 | |
14 | |
13 | |
9 |