DAX for comparing data

[Anonymous]

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

[amitchandak]

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.

[Anonymous]

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 · Accepted 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"
)

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[Anonymous]

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