cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Eped
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
v-caliao-msft
Microsoft
Microsoft

@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

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

Someone with ideas around this? 🙂

Anonymous
Not applicable

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

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.

 

 

Anonymous
Not applicable

@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

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

Anonymous
Not applicable

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

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? 

Anonymous
Not applicable

Hi @Eped

Please look again in the file. You have column called SwitchMeasure that is giving you both Sales and Gross Margin in the same column. Wasnt that what you needed? 

Br,

Gustav

Hi Gustav,

 

I want to show Net sales and GM the same pillar, yes. How can in do that? And of course a dynamic one, that is changing automaticly when you change the the data

I undertsand that Net sales and Gross is in the same column in your descpriction

Anonymous
Not applicable

@Eped

Everything will be dynamic and change when the data updates. What can I update in the file so that you can solve your problem?

In the solution .pbix I have done the following:

Have Net Sales and GM in the same column
- The measure for this is available aswell in the file
Have GM in % and Net Sales in numbers

/Gustav

Net sales.JPG

This is what i want. GM in the buttom of the pillar.

Anonymous
Not applicable

Hi again @Eped

I understand. I dont think that is possible. You can use GM as a tooltip but not display it like that in %. It is possible if you have Gross Profit as numbers only. You can use the line charts and use the GM % as a line and Net Sales as a pillar. I have updated the file with such an example (it will look good if you have dates on the x-axis, now its just a dot).

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

My solution only works if you use a table or a matrix (like a scorecard). 

Best regards,
Gustav

Thank you Gustav 🙂

 

I have treied yout solution before. In my case, I am going to use several pillars. As you can see at the picture below, it will be very messy and not visualizing a all.Capture.JPG

Do you have any idea on how this can be made better?

Anonymous
Not applicable

@Eped
I see the problem and why it looks messy.

In your case i would have GM as a tooltip when you hover the columns and use a table/matrix to display them next eachother. Can you maybe have something else with the netsales if you want to visualize the percentages? some costs or anything. 

Another solution would be to use two graphs next to eachother, one for GM and one for Net Sales. Try to make it simple, have a scorecard table, some graphs that show the changes in Net Sales and GM.

/Gustav

Thanks Gustav 🙂

 

I understand. This is actually what I did in the beginning, but wanted to improve. The thing is that I want as much information as possible in one slide/side. The pillars side by side take much space. Sad that there is no such function, that allows you different values, such as Net Sales and GM in the same pillar. Think that would be very nice. Specially when you want compare profitabilty in years, customers etc.  

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!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors