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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

amitchandak
Super User
Super User

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

 

 

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/

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, 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)

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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