Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
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? 🙂
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.
@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)?
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?
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
@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
This is what i want. GM in the buttom of the pillar.
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.
Do you have any idea on how this can be made better?
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |