Forum Discussion

Regular Visitor

6 years ago

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

Community Champion

6 years ago

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-TITHW/m-p/434008

Regular Visitor

6 years ago

Hi there,

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

Thanks

camargos88
Community Champion
6 years ago
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
  Regular Visitor
  6 years ago
  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
    Community Champion
    6 years ago
    Hi vjsingh2012 ,
    
    Try changing the number os months and instead of sum -> average.
    
    Ricardo