Need help with Line & Clustered Column Chart

[Anonymous]

Hi Everyone,

I'm relatively new to the BI platform and was hoping to get some help with a project I am working on. I can't disclose a lot of detail but will try to provide enough information to hopefully get pointed in the right direction. 

Essentially, I am trying to create a report that shows customer-specific data in comparison to the industry's regional trends (aggregate data of the same type compiled from all customers). I would like to show customer-specific data represented as columns with the aggregate data (average) shown as a line, all related to date on the shared axis. 

To do this, I created a separate copy of the data without any customer relationship. The chart works until I apply a customer filter to the chart. Once I do this, the aggregate data is also filtered by customer and it just shows me a customer average for the line series. I have reviewed the data model relationships and can't find anything linking it back to the customer. Is there a DAX function I should be using instead of creating a separate copy of the data table? Really stuck on this, any help you can give would be appreciated. 

Thank you!!

[samdthompson]

It will depend on which table your measure sits and which table the filter is on and the relationship direction. If you have a customer table which you are using a a filter, make sure that the relationship is 1:* not bi directional. also make sure that the axis holding the customer name comes from the customer table. In the measure make sure that the filtering refers to the customer table noth the fact table. These are just some things to check but its a bit stab in the darkish without seeing the table layout.

[samdthompson · Accepted solution]

Hello, I have some similar stuff I have to do around pricing. To tackle it i use two measures:

CustomerPrice = SUM(DataTable,DataTable[Price])

ComparisonPrice = CALCULATE([CustomerPrice], ALL(DataTable]))

I can then show the customers on the bars and the comparison as a line. You may want to be more nuanced about the comparison line ie you do want to vary regionally or some such. In that case it might be something like:

ComparisonPrice =

CALCULATE([CustomerPrice],FILTER(ALL(Customer),Customer[Region]=SELECTEDVALUE(Customer[Region]))).

Hope this points you in the right direction anyway.

[Anonymous]

I've followed up on your tips to use the Calculate function and I can now show an aggregate average that is not affected by a customer slicer as long as I keep that average on a separate visual. However, once I combine the average along with the customer specific data onto the same chart (clustered column and line) then the customer filter affects the aggregate measure. I can't figure out how to get these two pieces of info on the same chart. Any other thoughts?

[Anonymous]

Okay, I will give that a shot! Thank you