concatenate text field of a alphanumeric field based on another field

[sadiaasghar]

I want to concatenate text value of a alphanumeric field i.e. Progress Description /Achievement based on month and project in a single row.   I want to display it on current Month, Current year basis but the problem is some project has sub projects so they have multiple values for a single month i want them to concatenate by a line break. Or if the value is numeric i want to show cumulative progress description for that specific month.

As you can see in image i want them in a single line item against each project.

[Kushal_Chawda · Accepted solution]

@sadiaasghar  You may need to create the additional dimension and add it in your straight table as you want to perform different aggregation based on condition. Hence, you need additional dimension in your table to work correctly. You can hide that dimension if you want but I would advise to keep it as it makes sense 

data:
LOAD [Fiscal Year], 
     [Measure/Department], 
     Project, 
     [Sub-Project], 
     Month, 
     if(IsText([Progress Description]),'Text','Number') as Progress_Description_Category,
     [Monthly Step weightage], 
     [Overall Step Weightage], 
     [Progress Target], 
     [Progress Description]
FROM
[C:\Data for concatenate.xlsx]
(ooxml, embedded labels, table is Sheet1);

Now you can create straight table 

Dimensions:

[Fiscal Year],
[Measure/Department],
Project

Progress_Description_Category // you can hide it  from presentation if don't want

Expression:

=if(Progress_Description_Category='Text', Concat(distinct [Progress Description],chr(10)),sum( [Progress Description]))