Forum Discussion
DAX for comparing data
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.
Super UserTwo 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.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