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 there,
I have data in below mentioned format:
Product | Profit | Year |
A | 53.28% | 2014 |
B | 46.57% | 2014 |
C | 42.35% | 2014 |
D | 63.32% | 2014 |
E | 64.43% | 2014 |
F | 22.92% | 2014 |
G | 50.48% | 2014 |
A | 59.37% | 2015 |
B | 38.22% | 2015 |
C | 44.00% | 2015 |
D | 58.23% | 2015 |
E | 61.22% | 2015 |
F | 26.69% | 2015 |
G | 40.34% | 2015 |
Combine entity | 49.05% | 2014 |
Combine entity | 51.37% | 2015 |
I want to present data in line graph so that we can compare profit of selected product category against average profit of other 6 product. So that it shows how the select product has performed against all remaning products.
Presently I have created a combine entity with average of all for both year. So the graph is comparing the selected product with combine entity. But is there a way to compare a product with the other 5 product as single entity?
Thank you
Solved! Go to Solution.
@Anonymous -
One way you can accomplish this would be to add a Calculated Column to make groupings of your Categories:
Parent Category = IF( [Category] = "A", "A", "Other" )
And then use the new Calculated Column as the Legend on your line chart, along with a simple Average as your value.
Otherwise, you could create 2 separate measures and add them both to your line chart:
Average Profit (A) = CALCULATE( AVERAGE(YourTable[Profit]), YourTable[Category] = "A" )
Average Profit (Not A) = CALCULATE( AVERAGE(YourTable[Profit]), YourTable[Category] <> "A" )
Two things can help
1. Getting overall Avg: https://community.powerbi.com/t5/Desktop/How-to-Calculate-Average-of-Total-in-Power-BI/td-p/253838
2. In the latest version, right-click on, measure and choose Quick Measure and then choose Avg on a category. That should give you a category.
These two will help you to avoid the row you added.
Hey @amitchandak,
Thanks for the article, it was informative. But i want to compare one category vs the other 6. Like in the Data there are 7 categories from A, B, C,D,E,F & G.
Is there a way so that I can show on a line chart the value of A across years and avg value of B,C, D,E,F&G as whole (only 2 lines in chart)?
Regards,
Saket
@Anonymous -
One way you can accomplish this would be to add a Calculated Column to make groupings of your Categories:
Parent Category = IF( [Category] = "A", "A", "Other" )
And then use the new Calculated Column as the Legend on your line chart, along with a simple Average as your value.
Otherwise, you could create 2 separate measures and add them both to your line chart:
Average Profit (A) = CALCULATE( AVERAGE(YourTable[Profit]), YourTable[Category] = "A" )
Average Profit (Not A) = CALCULATE( AVERAGE(YourTable[Profit]), YourTable[Category] <> "A" )
Hey @Anonymous thanks for this!
It is working for category A vs all other category.
But how to make is dynamic. Like I am thinking of selecting the category from a slicer. Is there a way to make it dynamic? So that if user has selected category B from slicer, the graph shows 2 lines. one for B and another average of all excluding B and similar for rest of the category.
Regards,
Saket
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |