cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Data Grouping_Query...

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

 

MonthCountryRevenue
2018-01-01South Africa71161
2018-01-01Australia71846
2018-01-01New Zealand82362
2018-01-01Canada14571
2018-01-01Mexico20240
2018-01-01France28964
2018-01-01Germany34100
2018-01-01Holland56968
2018-01-01Dubai73077
2018-01-01India44403
2018-01-01Singapore32259
2018-01-01Hong Kong70892
2018-01-01Malaysia50422
2018-01-01Thailand78815
2018-01-01UK94511
2018-01-01Spain75883
2018-01-01USA26079
2018-02-01South Africa88730
2018-02-01Australia73335
2018-02-01New Zealand98753
2018-02-01Singapore56061
2018-02-01Hong Kong63835
2018-02-01Malaysia29817
2018-02-01India46900
2018-02-01France29199
2018-02-01Germany24647
2018-02-01Holland79332
2018-02-01Dubai52795
2018-02-01Spain49750
2018-02-01UK90664
2018-02-01Canada52331
2018-02-01Mexico92243
2018-02-01USA27159
2018-03-01South Africa33810
2018-03-01Australia35929
2018-03-01New Zealand72659
2018-03-01Canada27049
2018-03-01Mexico76099
2018-03-01India39810
2018-03-01Singapore15855
2018-03-01Hong Kong42981
2018-03-01Malaysia30716
2018-03-01UK49218
2018-03-01Spain53436
2018-03-01France56855
2018-03-01Germany33423
2018-03-01Holland56958
2018-03-01Dubai77362
2018-03-01USA14603
2018-04-01Australia69685
2018-04-01South Africa14654
2018-04-01New Zealand37165
2018-04-01Mexico87636
2018-04-01Canada53901
2018-04-01Singapore44587
2018-04-01Hong Kong50680
2018-04-01Malaysia54035
2018-04-01Germany61755
2018-04-01France65076
2018-04-01Holland98959
2018-04-01Dubai71195
2018-04-01India46445
2018-04-01Spain75283
2018-04-01UK56194
2018-04-01USA85050
2018-05-01South Africa15042
2018-05-01Australia99610
2018-05-01New Zealand36147
2018-05-01Singapore21539
5 REPLIES 5
Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi there,

 

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

 

Thanks

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



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.

Hi @vjsingh2012 ,

 

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

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.