Measure on matrix row

[bml123]

Hi,

I have the data as below in a matrix where Type is in Rows and Amount is in columns.

Type	Amount
Shampoo	100
Clothes	50
	70

Whereever there is space, I need to get 'Others' in Type. Type is in rows in the matrix. My expected output is 

Type	Amount
Shampoo	100
Clothes	50
Others	70

How do I achieve that?

[v-henryk-mstf · Accepted solution]

Hi @bml123 ,

Convert the above formula from MEASURE to COLUMN whether it meets your needs, refer to the following:

Column =
IF ( 'Table'[Type] = BLANK () || 'Table'[Type] = "", "Other", 'Table'[Type] )

If the problem is still not resolved, please point it out. Looking forward to your feedback.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bml123]

Sorry @MarkLaf , it didn't work with calculated column, it is pulling correct with the measure but unable to use it in row in the matrix

[MarkLaf]

Perhaps you can share a snapshot of your model daigram and/or provide a small set of example data? I assumed data like the below when drafting my response:

Type	Amount
Shampoo	10
Shampoo	90
Clothes	30
Clothes	20
	45
	25

Also, please provide a little more info on what "not working" means. E.g. Is the column not producing "Other" where originally blank? Or it looks correct in your table, "Other" shows up when you add rows in your matrix, but the values don't appear correctly?

Finally, quick note that your solution will need to get data in a column somehow, rather than use a measure, as only physical columns can be placed in a Matrix row.

[MarkLaf]

You can add a calculated column to your Table with the types column, and use that in your matrix visual:

Types_Label = IF( ISBLANK( 'Table'[Type] ) || 'Table'[Type] = "", "Other", 'Table'[Type] )

 Note: not sure if the blank is empty text or an actual blank/null, so checking for both, but you can just check for the one if you know which it is in your data.

You can then rename 'Types_Label' to just 'Type'  for the particular visual.