cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
admin11
Post Prodigy
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 ? 
 

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
selimovd
Community Champion
Community Champion

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
Community Champion
Community Champion

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

 

selimovd
Community Champion
Community Champion

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

 

selimovd
Community Champion
Community Champion

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

@selimovd 

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

Paul

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors