3 Replies Latest reply on Nov 30, 2018 3:48 AM by Yuan Howe Liak

# Assigning a category based on a aggregate condition then counting members in category

I have a table in this format:

CustomerMonthSales
A1947
A2234
A3423
B1754
B2355
B3897

And I would like to:

1. Assign a category (call it Gold, Silver Bronze) to [Customer] based on the a conditional 'if' of the sum of all months filtered, then

2. Have a table with each Category as row label and count([Category]) as the measure

I feel like this should be simple to do but the inability to do something like 'if sum([Sales])>100000 then 'Gold' elseif sum([Sales]>50000 then 'Silver'...' is making me run into a wall..

• ###### 1. Re: Assigning a category based on a aggregate condition then counting members in category

Hi Yuan,

Please find with solution,

Regards,

Babu

• ###### 2. Re: Assigning a category based on a aggregate condition then counting members in category

You will need to use LOD calculations to get results

e.g. { fixed [Customer]:sum([Sales])}

this will calculate sales total for customers ignoring other granularity and use this for you category calculation

Please find the attached workbook for your reference.

1 of 1 people found this helpful
• ###### 3. Re: Assigning a category based on a aggregate condition then counting members in category

D'oh! In retrospect it's so obvious to just separate the if statements and the LOD sum() calculations. I was trying to combine the two

Thank you!