Forum Discussion

vjsingh2012's avatar
vjsingh2012
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...
aaa
vjsingh2012's avatar
vjsingh2012
Regular Visitor
6 years ago

Hi there,

 

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

 

Thanks

camargos88's avatar
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