Data Grouping_Query...

[vjsingh2012]

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

[Greg_Deckler]

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

[vjsingh2012]

Hi there,

Thanks for prompt reply. data text as copy pasted, please check.

Thanks

[camargos88]

Hi @vjsingh2012 ,

Use this code to create a new column:

GroupTier =

VAR _date = EOMONTH(MAX('Table (4)'[Month]);-3) + 1

VAR _total = CALCULATE(SUM('Table (4)'[Revenue]); FILTER(ALLEXCEPT('Table (4)'; 'Table (4)'[Country]); 'Table (4)'[Month] >= _date))

RETURN IF(_total <= 50000; "Tier 4"; IF(_total <= 100000; "Tier 3"; IF(_total <= 200000; "Tier 4"; "Tier 5")))

If not necessary the grouping by country just change this code:

VAR _total = CALCULATE(SUM('Table (4)'[Revenue]); FILTER(ALL('Table (4)'[Month]); 'Table (4)'[Month] >= _date))

Ricardo

[vjsingh2012]

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.

[camargos88]

Hi @vjsingh2012 ,

Try changing the number os months and instead of sum -> average.

Ricardo