How to recode my expense table into few expense group ?

[admin11]

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

[selimovd]

Hi @admin11 ,

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[admin11]

@selimovd 

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.

[selimovd]

Helo @admin11 ,

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[admin11]

@selimovd 

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

[selimovd · Accepted solution]

Hey @admin11 ,

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[admin11]

@selimovd 

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

Paul