Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
admin11
Memorable Member
Memorable Member

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 ? 
 

admin11_0-1618922320655.png

 

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

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:

selimovd_0-1618928784980.png

 

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
 

View solution in original post

6 REPLIES 6
selimovd
Super User
Super User

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
 

@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.

admin11_0-1618925032224.png

 

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
 

@selimovd 

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

admin11_0-1618926179568.png

 

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:

selimovd_0-1618928784980.png

 

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
 

@selimovd 

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

Paul

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.