Adding the existing 2 or more category into additional category

[Anonymous]

Hi All,

Provided my dataset

Emp Id and Age is the orignial coulmns. 

Age category(Bucket) is the column which was created with the help of Age Column.

Emp count is the measure i.e., Total emp counts.

Now i want to 2 more categories.

one that will sum up everything above 10 i.e., sum up the 3 categories (11 to 15 + 16 to 20 + 21 and Above)

another one that will sum up everything above 15 i.e., sum up the 2 categories (16 to 20 + 21 and Above)

Provided below my req. output in power bi

Please let me know how to achieve Updated Age Category column in power bi. i.e., i believe we need to create a calculated column in Power BI.

Thanks in Advance

Regards,

Prakash M

[TomMartens · Accepted solution]

Hey @Anonymous ,

as one employee can be assigned to multiple age groups you enter the realm of many-to-many relationships.

For this reason, you have to adapt your data model.

I used this DAX statement to create a table called age groups:

age groups = 
var agemax = MAX('Table'[Age])
return
UNION(
    GENERATEALL(
        ROW("age group" , "0 to 5" , "sort" , 1)  , GENERATESERIES(0 , 5 , 1)
    )
    , GENERATEALL(
        ROW("age group" , "6 to 10" , "sort" , 2)  , GENERATESERIES(6 , 10 , 1)
    )
    , GENERATEALL(
        ROW("age group" , "11 to 15" , "sort" , 3)  , GENERATESERIES(11 , 15 , 1)
    )
    , GENERATEALL(
        ROW("age group" , "16 to 20" , "sort" , 5)  , GENERATESERIES(16 , 20 , 1)
    )
    , GENERATEALL(
        ROW("age group" , "21+"  , "sort" , 7)  , GENERATESERIES(21 , agemax , 1)
    )
    , GENERATEALL(
        ROW("age group" , "11 and above" , "sort" , 4)  , GENERATESERIES(11 , agemax , 1)
    )
    , GENERATEALL(
        ROW("age group" , "16 and above" , "sort" , 6)  , GENERATESERIES(16 , agemax , 1)
    )
)

I created another table I call age. This table is used only to create relationships between the employee | age table I call Table. The table age can be hidden. This is the DAX to create the age table:

age = 
DISTINCT(
    ALLNOBLANKROW('Table'[Age])
)

Then I create relationships , be aware of the filter direction Both between the age table and the age groups table:

This allows to simply create this table visual, no calculated columns or measures are needed:

There is one thing to note, as there is no direct relationship between the tables Table (Employee ID | Age) and age groups, you will be hinted that there is no direct relationship between the table, and it will seem that something did break. But as soon you change the aggregation function of the column Employee ID to  Count (Distinct) everything will work as expected.

Personally I prefer to answer questions by the data model and avoid creating calculated columns or measure.

Regards,
Tom

[amitchandak]

@Anonymous , you can create a new column using switch

switch( true()
[age]<= 5 ," 0-5",
[age]< 10 ,"06 10"

// keep on adding conditions
)

[BA_Pete]

Hi @Anonymous ,

This sounds like dynamic segmentation is required.

Try following this guide at daxpatterns.com: Dynamic Segmentation

Pete