Forum Discussion

Jumpin1c4's avatar
Jumpin1c4
New Member
1 year ago
Solved

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.

  • Anonymous's avatar
    Anonymous
    1 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

    • Jumpin1c4's avatar
      Jumpin1c4
      New 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?  

    • Jumpin1c4's avatar
      Jumpin1c4
      New 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!

       

       

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

  • Anonymous's avatar
    Anonymous
    Not 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.