Need help combining categories of a column into a measure and renaming them

[RichOB]

Hi, I need to group the 3 general ledger codes below to "Income" and have a slicer with the location. 

I have grouped them by using:

Income_ = CALCULATE(

    SUM('Table'[Amount]),

    KEEPFILTERS(Table [General_Ledger_Code] IN {"101", "102", "103"}))

How do I rename these as Income within the measure? I thought that they would appear as the title name "Income_", but they are listing individually in a matrix table.

Orignial data table

General_Ledger_Code	Name	Amount	Location
101	Donations	1000	Manchester
102	Contract Income	50,000	Manchester
103	Investment Income	100,000	Manchester
101	Donations	20,000	Edinburgh
102	Contract Income	30,000	Edinburgh
103	Investment Income	40,000	Edinburgh

This is how I want it to look

Type	Locaton	Total
Income	Manchester	160,000
Income	Edinburgh	90,000

Thanks

[v-veshwara-msft]

Hi @RichOB ,

Following up to see if your query has been resolved. If any of the responses helped, please consider marking the relevant reply as the 'Accepted Solution' to assist others with similar questions.

If you're still facing issues, feel free to reach out.

Thank you.

[v-veshwara-msft]

Hi @RichOB ,

Thank you for engaging with the Microsoft Fabric Community.

Just following up to check if your query has been resolved and whether the responses were helpful. If so, please consider marking the helpful replies as Accepted Solutions to assist others with similar queries.

If further assistance is needed, please reach out.
Thank you.

[danextian]

Hi @RichOB 

Your measure creates aggregates the amount column based on GL codes that are you in your list of Income codes. It doesn't identify the code as whether it is income or not.

You can try this measure:

IF (
    SELECTEDVALUE ( 'Table'[General_Ledger_Code] ) IN { "101", "102", "103" },
    "Income",
    "Other"
)

Please note that measures cannot be used as a dimension or in a slicer as their result depends on the other dimension added to a visual. If you want to be able to use that as a dimension, create a calculated column instead

IF (
    'Table'[General_Ledger_Code] IN { "101", "102", "103" },
    "Income",
    "Other"
)

Use the calculated column in your visual.

[MattiaFratello]

Hi @RichOB, why not creating a group or even a new column?

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning

https://learn.microsoft.com/en-us/power-query/add-conditional-column

Then you can use either the group or the new column in your matrix

Please let me know if you need additional examples

[ChielFaber · Accepted solution]

The easiest way is to add a calculated column to your table. Click add column and as an example you can use the following dax code:

Type =
SWITCH(
TRUE(),
'Table'[General_Ledger_Code] IN {"101", "102", "103"}, "Income",
"Other"
)

This will add a new column to the data that states Income. You can then drop this into your visual.