Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi experts,
I am a learner of Power BI and Power Pivot. So forgive me if my query looks silly.
So here is my query. I have a dataset with three columns Months, Regions & Revenue. We have almost two years (month wise) revenue data for multiple reasons. I need to show data in Power Pivot grouped as "Tier 1, Tier 2, Tier 3 & Tier 4" based on below criteria.
Criteria Tier
<=50000 Tier 4
<=100000 Tier 3
<=200000 Tier 2
>=200000 Tier 1
Let me explain how to decide which region fall in which Tier. Take the sum of last three months (last month decided based on maximum date) and check above criteria and decide Tier. I am unable to attach data file hence below copy pasted data as text.
Please see below data as text.....
Month | Country | Revenue |
2018-01-01 | South Africa | 71161 |
2018-01-01 | Australia | 71846 |
2018-01-01 | New Zealand | 82362 |
2018-01-01 | Canada | 14571 |
2018-01-01 | Mexico | 20240 |
2018-01-01 | France | 28964 |
2018-01-01 | Germany | 34100 |
2018-01-01 | Holland | 56968 |
2018-01-01 | Dubai | 73077 |
2018-01-01 | India | 44403 |
2018-01-01 | Singapore | 32259 |
2018-01-01 | Hong Kong | 70892 |
2018-01-01 | Malaysia | 50422 |
2018-01-01 | Thailand | 78815 |
2018-01-01 | UK | 94511 |
2018-01-01 | Spain | 75883 |
2018-01-01 | USA | 26079 |
2018-02-01 | South Africa | 88730 |
2018-02-01 | Australia | 73335 |
2018-02-01 | New Zealand | 98753 |
2018-02-01 | Singapore | 56061 |
2018-02-01 | Hong Kong | 63835 |
2018-02-01 | Malaysia | 29817 |
2018-02-01 | India | 46900 |
2018-02-01 | France | 29199 |
2018-02-01 | Germany | 24647 |
2018-02-01 | Holland | 79332 |
2018-02-01 | Dubai | 52795 |
2018-02-01 | Spain | 49750 |
2018-02-01 | UK | 90664 |
2018-02-01 | Canada | 52331 |
2018-02-01 | Mexico | 92243 |
2018-02-01 | USA | 27159 |
2018-03-01 | South Africa | 33810 |
2018-03-01 | Australia | 35929 |
2018-03-01 | New Zealand | 72659 |
2018-03-01 | Canada | 27049 |
2018-03-01 | Mexico | 76099 |
2018-03-01 | India | 39810 |
2018-03-01 | Singapore | 15855 |
2018-03-01 | Hong Kong | 42981 |
2018-03-01 | Malaysia | 30716 |
2018-03-01 | UK | 49218 |
2018-03-01 | Spain | 53436 |
2018-03-01 | France | 56855 |
2018-03-01 | Germany | 33423 |
2018-03-01 | Holland | 56958 |
2018-03-01 | Dubai | 77362 |
2018-03-01 | USA | 14603 |
2018-04-01 | Australia | 69685 |
2018-04-01 | South Africa | 14654 |
2018-04-01 | New Zealand | 37165 |
2018-04-01 | Mexico | 87636 |
2018-04-01 | Canada | 53901 |
2018-04-01 | Singapore | 44587 |
2018-04-01 | Hong Kong | 50680 |
2018-04-01 | Malaysia | 54035 |
2018-04-01 | Germany | 61755 |
2018-04-01 | France | 65076 |
2018-04-01 | Holland | 98959 |
2018-04-01 | Dubai | 71195 |
2018-04-01 | India | 46445 |
2018-04-01 | Spain | 75283 |
2018-04-01 | UK | 56194 |
2018-04-01 | USA | 85050 |
2018-05-01 | South Africa | 15042 |
2018-05-01 | Australia | 99610 |
2018-05-01 | New Zealand | 36147 |
2018-05-01 | Singapore | 21539 |
Sample data as text please. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
See if my Time Intelligence the Hard Way provides a way of accomplishing what you are going for (last 3 months).
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
Hi there,
Thanks for prompt reply. data text as copy pasted, please check.
Thanks
Hi @vjsingh2012 ,
Use this code to create a new column:
Thank you Camargos....completely resolved my query. Thanks
However, we have got a change request in deciding Tier. Earliar we were taking sum of last three months and than checking Tier criteria. Now we have to take average of last two quarter and than establish the tier with the same Tier Amount as before.
I tried to do it myself with the Average/AverageX both function but getting error.
Thanks again for helping.
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |