Newbie Question Help

[Jumpin1c4]

Any help or advice would be appreciated.  I have a table with a list of devices for various customers.  I am trying to categorize the customers by the average age of their devices.  Customers with an average age of 0 to 2, 2 to 5 and over 5.  I created a calculated column that looks at the average age of the devices for each customer and then marks all devices for that customer with the customer average age.  So if customer A has 10 devices  with an avergage age of 3.2 all ten devices in the calculated column will be labeled with the customer average age of 3.2.  Then I created another calculated column that looks at the customer average age and results in each device for that customer marked with the proper age group.  So all 10 devices in the table would be marked as the customer age group of 2 to 5 years.  This works at a high level but I run into issue when using slicers that chaneg the number of devices for the customer.  Customers are in different geos and if I slice on a geo  the calulated customer average age and age grouping do not adjust for the slicer.  In my example, if I choose North America and half the devices are taken out it might result in a change in the average customer age in that geo for that customer to be different from the customer average age and age grouping created in the calculated column.  The way I have it now the custustomer average age and age grouping do not adjust to the slicer. Is there a way to do this that works so the effect of the slicer adjusts the customer average age properly and assigns the proper age grouping when I slice by geo?  Any help would be appreciated.  I've tried to find an answer but am coming up empty.

[Anonymous · Accepted solution]

Hi @Jumpin1c4 ,

Thanks to SachinNandanwar  and audreygerred  for their quick replies. I have some other thoughts to add:

(1) This is my test data.

(2) We can create measures.

CustomerAvgAge = 
CALCULATE(
    AVERAGE('Devices'[DeviceAge]),
    ALLEXCEPT('Devices', 'Devices'[CustomerID])
)

CustomerAgeGroup = 
SWITCH(
    TRUE(),
    [CustomerAvgAge] <= 2, "0 to 2 years",
    [CustomerAvgAge] <= 5, "2 to 5 years",
    "Over 5 years"
)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Jumpin1c4]

I try to add the excel file but it says that file type is not supported

[audreygerred]

Hi! Calculated columns are processed at the time of the data load and therefore are not dynamic. You will need to create measures so that the output dynamically changes with filtering, grouping, etc. Calculated Columns and Measures in DAX - SQLBI

[Jumpin1c4]

Thanks, I'm aware of that now but do not know how to create a measure that will function like the calculated column I already have.    But I will keep trying!

[SachinNandanwar]

Please post some sample data and the expected output.

[Jumpin1c4]

Forgive my ignorance, what do you mean by post sample data? I have my data in excel files that are feeding my Power BI Desktop program.  Do you want a sample of the excel file?  

[SachinNandanwar]

Yes.