cancel
Showing results for
Did you mean:
Post Prodigy

## 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

Post Prodigy

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

Post Prodigy

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

Post Prodigy

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

Paul

Announcements