Forum Discussion
Newbie Question Help
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.
- Anonymous1 year ago
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.
7 Replies
- SachinNandanwarImpactful Individual
Please post some sample data and the expected output.
- Jumpin1c4New Member
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?
- SachinNandanwarImpactful Individual
Yes.
- audreygerredSuper User
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
- Jumpin1c4New Member
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!
- Jumpin1c4New Member
I try to add the excel file but it says that file type is not supported
- AnonymousNot applicable
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.