cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

I want show the data based on category in single column

I have sample data and i want show the data based on category.
Please let me know how can I do that. Apprecaite any help.

actual.PNGexpected.PNG

1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

Hi @Anonymous ,

 

We can use the first row as header and do some transformations in Power Query Editor to meet your requirement.

 

1. Use first row as headers.

 

I1.jpg

 

2. Then create a conditional column.

 

I2.jpg

 

3. At last delete the column 2 and column 3.

 

I3.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

View solution in original post

7 REPLIES 7
v-zhenbw-msft
Community Support
Community Support

Hi @Anonymous ,

 

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-zhenbw-msft
Community Support
Community Support

Hi @Anonymous ,

 

We can use the first row as header and do some transformations in Power Query Editor to meet your requirement.

 

1. Use first row as headers.

 

I1.jpg

 

2. Then create a conditional column.

 

I2.jpg

 

3. At last delete the column 2 and column 3.

 

I3.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

View solution in original post

amitchandak
Super User IV
Super User IV

@Anonymous , If income and expense are two measures

ass a new measure

sumx(Table, if(max(Table[Type]) = "Operating Expense", [Expense][Income])

 

Can you share sample data and sample output in table format?

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Anonymous
Not applicable

Category typeINCOMEEXPENSE 
Operating Expence   
ROU Asset00 
Acc Deprn - ROU Asset00 
Berenberg Bank - USD00 
Prepayments (others)00 
Time charter expense (TC IN)058000 
Operating Income   
Time Charter Income480000 
Brokers Commission00 
Owners payable account00 
EXPECTED  
Operating ExpenceExpected Values 
ROU Asset0 
Acc Deprn - ROU Asset0 
Berenberg Bank - USD0 
Prepayments (others)0 
Time charter expense (TC IN)58000 
Operating Income  
Time Charter Income48000 
Brokers Commission0 
Owners payable account0 

 

 

I don't know if I fully understand your requirements, did you just want to add income and expenses?

See if these posts on PL in Power BI might help you

https://www.thebiccountant.com/2017/01/16/easy-profit-loss-and-other-account-statements-part2/

https://blog.enterprisedna.co/how-to-create-profit-and-loss-statements-in-power-bi/


?? Check out my March Madness Report??


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Anonymous
Not applicable

@AllisonKennedy  We allready have income and expenses cost category, in our requirement  we have to show income and expense data for corresponding cost category in a single column.

Thanks in advance.

I assume you still want a way to differentiate between them? You can create a Calculated COLUMN that simply adds the two values:
Value = Table[Expense] + Table[Income]
But if you want to differentiate between them, then you might be able to get what you want by doing an UNPIVOT in the raw data inside Power Query Editor, so that instead of Expense and INcome columns, you have Attribute and Value.
Then you can use a Matrix visual and put the Value in Values, and use the Attribute when needed to differentiate between income and expense (ie put Attribute in Rows, then Category type underneath it in Rows, with Value in Values)


?? Check out my March Madness Report??


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

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

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.