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

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
 
admin11
Impactful Individual
Impactful Individual

@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
 
admin11
Impactful Individual
Impactful Individual

@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

admin11
Impactful Individual
Impactful Individual

@selimovd 

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

Paul

Helpful resources

Announcements
MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

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 768x460.png

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