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 🙂
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.
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
Hi @EpedWhen I want to achieve this I usually create a table with a SortKey column like this:
1 Net Sales
2 Gross Profit
Then i create a Switch Measure:
,"Gross Profit",FORMAT([Gross Profit],"0.00%")
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!
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.
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 🙂
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:
Thanks a lot Gustav 🙂
I am struggeling with the formula: IF(HASONEVALUE([SalesType])
,"Gross Profit",FORMAT([Gross Profit],"0.00%")
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?
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?
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
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
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).
My solution only works if you use a table or a matrix (like a scorecard).
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.
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.
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.
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Check out how to claim yours today!
Test your skills now with the Cloud Skills Challenge.