cancel
Showing results for
Did you mean:
Impactful Individual

## How to recode my expense table into few expense group ?

Hi All

I have a expense table , it was filter using below expression :-

1_EXP =
SWITCH(TRUE(),
'GL'[Reporting Code]>=00100 &&'GL'[Reporting Code]<=00375,"EXP",
BLANK())

Now i need break the table into many group , for example
Reporting Code = 50  , 41  recode as EXP1
Reporting Code = 40  , 39  recode as EXP2

So that i can know the expense increase is from which expense type ?

Hope some one can share with me how to write the column.

https://www.dropbox.com/s/4jokce4a65qszic/PBT_V2021_400%20GL_TI.pbix?dl=0

Paul Yeo

1 ACCEPTED SOLUTION
Community Champion

I checked the data, you don't get any result because you don't have rows with GL[Reporting Code] 38, 40, 41 or 50.

If you change the formula to 101 or 164, then a result will appear:

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

6 REPLIES 6
Community Champion

I think you already did most of the work.

To break it down to Exp1 and Exp2 you just have to add it to the switch:

``````1_EXP =
SWITCH(
TRUE(),
'GL'[Reporting Code] >= 00100 && 'GL'[Reporting Code] <= 00375, "EXP",¨
'GL'[Reporting Code] = 50 || 'GL'[Reporting Code] = 41, "EXP1",
'GL'[Reporting Code] = 40 || 'GL'[Reporting Code] = 38, "EXP2",
BLANK()
)``````

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Impactful Individual

Thank you for sharing , it is what i need , but when i apply your code into my pbi file , the EXP1 and EXP2 not appear.

Community Champion

sure, because the first criteria that fits will be taken.

So if you remove the following line, it should work:

``'GL'[Reporting Code] >= 00100 && 'GL'[Reporting Code] <= 00375, "EXP"``

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Impactful Individual

Sorry to trouble you again , it still no luck , EXP1 and EXP2 still not appear

Community Champion

I checked the data, you don't get any result because you don't have rows with GL[Reporting Code] 38, 40, 41 or 50.

If you change the formula to 101 or 164, then a result will appear:

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Impactful Individual

Thank you very much , now my P&L expense table can be summary report.

Paul

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

#### Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors