cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

How to get Net Sales and Gross Profit in the same Column/pillar in the visualization of Power BI

Hi,

 

Hopefully someone can help with this. For example: How to get Gross Profit (as antoher collor and in percent) in the same column/pillas as Net Sales? I have tried with calculations without luck. Thanks in advance 🙂

20 REPLIES 20
Highlighted
Microsoft
Microsoft

Re: How to get Net Sales and Gross Profit in the same Column/pillar in the visualization of Power BI

@Eped,

 

Could you please elaborate the structure of your data model? How many table do you have? And which colums do you have inside each table? If you could provide us some sample data together with your data model structrue, we could make further analysis and provide yo the detail DAX expression for your requirement.

 

Regards,

Charlie Liao

Highlighted
Helper I
Helper I

Re: How to get Net Sales and Gross Profit in the same Column/pillar in the visualization of Power BI

Hi,

 

My data is from one excelsheet, with one customer, and with the products we are selling to them. Then we have Net Sales and Gross Profit in the same sheet. I dont have many colums inside each table. Max 5. By the way, I found Gross Margin by do this calcultion:

GM15 = DIVIDE(SUM(Sheet1[Sum of GP15]);(SUM(Sheet1[Sum of NS15])))

Thats works fine. Now, I want "GM15" in the same Column/pillar as Net Sales (in the buttom of the pillar). I am able to get Net Sales and GM15 side by side as a pillar, but it would be much better to get this two pillars into one

Highlighted
Helper I
Helper I

Re: How to get Net Sales and Gross Profit in the same Column/pillar in the visualization of Power BI

Someone with ideas around this? 🙂

Highlighted
Resolver I
Resolver I

Re: How to get Net Sales and Gross Profit in the same Column/pillar in the visualization of Power BI

Hi @EpedWhen I want to achieve this I usually create a table with a SortKey column like this:

SortKey    SalesType
1               Net Sales
2               Gross Profit

Then i create a Switch Measure:

IF(HASONEVALUE([SalesType])
,SWITCH(VALUES([SalesType])
,"Net Sales",[NetSales]
,"Gross Profit",FORMAT([Gross Profit],"0.00%")
,BLANK()))

By doing this you should be able to get these under the same column and also in different formats. Not sure how you can have different colors. Please try this and see if it works out for you!

Best regards,
Gustav

Highlighted
Helper I
Helper I

Re: How to get Net Sales and Gross Profit in the same Column/pillar in the visualization of Power BI

Thank for the answer Gustav!

Hopefully you can answer this too.

How do I make a SortKey column? I dont find out how, when i try to google it.

 

 

Highlighted
Resolver I
Resolver I

Re: How to get Net Sales and Gross Profit in the same Column/pillar in the visualization of Power BI

@Eped

In this example I would just create a new table that has two columns.

-> Click Enter Data and add the values that I gave you above:
One is named SortKey in which you populate your preferred sortorder: 1 & 2 and so on.
One is named SalesType with your MeasureNames. 

The SortKey column is just a random column with numbers (because its easy to sort by). Thats nothing special in Power BI so you shouldnt find anything about it 🙂

/Gustav

Highlighted
Helper I
Helper I

Re: How to get Net Sales and Gross Profit in the same Column/pillar in the visualization of Power BI

Thank you! I am still struggeling a bit here. How do I connect this to the values i have (Net Sales and Gross profit)?

Highlighted
Resolver I
Resolver I

Re: How to get Net Sales and Gross Profit in the same Column/pillar in the visualization of Power BI

Hi @Eped

For example:

Create a Table or Matrix visual. Put in the Sales Type column that you created and then create the measure that i described above. Then you should be able to get right. 

I did create an example PBIX file that shows you the solution:

https://1drv.ms/u/s!AgUIGKdICZfnjhe3s540ds57GxQ7

Best regards
Gustav

Highlighted
Helper I
Helper I

Re: How to get Net Sales and Gross Profit in the same Column/pillar in the visualization of Power BI

Thanks a lot Gustav 🙂

 

I am struggeling with the formula: IF(HASONEVALUE([SalesType])
,SWITCH(VALUES([SalesType])
,"Net Sales",[NetSales]
,"Gross Profit",FORMAT([Gross Profit],"0.00%")
,BLANK())).

 

Can you show me this in the PBIX file as well? How its look like when you get Net Sales and Gross margin in the same column/pillar. Can you show me? 

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors