Summarising Two Fields into One

[dapling]

I am new to PowerBI, having been a reasonably advanced Excel user, particularly pivots.

I have two fields - Type and Scheme.

In Type, every record is either A, B and C.

In Scheme, I have fields 1 thro 20, which are text.  Examples of Scheme are X:abc, X:def, X:ghi, abc, def, ghi, etc. 

I want to summarise by a new field called 'Summary'.

The rule for Summary is 'if Scheme begins "X:", then X, otherwise Type'.

Output will alow me to summarise my data by X, a, B and C, where a + X = A.  'a' being what is left of A that is not now tagged as X.

Grateful of any help here!  I can't figure out how to use DAX and whether it's a new column (my assumption) or a measure...!!

Thanks, DP

[v-haibl-msft]

@dapling

Your first requirement should can be resolved by the solution provided by KGrice.

For your second requirement, do you mean you want to change “a” which in a column to “A”, if another column data is “X”? If yes, you can creat a new column using the formula like: IF ( [X Column] = “X”, UPPER ( [a column] ), [X Column] )

Best Regards,

Herbert

[KGrice · Accepted solution]

You could do this in the Query Editor or by adding a new column in the Data View. For the Query Editor:

From the main report view, go to the Home tab and click Edit Queries to get to the Query Editor. Find your query where you want the new column, then go to Add Column in the ribbon and click Add Custom Column. In the new window, change the New column name to Summary and enter the formula below:

=if Text.Start([Scheme], 2) = "X:" then "X" else [Type]

If you want to do it in Data View as a new column, you could use this formula:

=IF(LEFT(TableName[Scheme], 2)="X:", "X", TableName[Type])