Forum Discussion
DAX for comparing data
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
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" )I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
4 Replies
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" )I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
