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.
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 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