Line and Clustered column chart issue

[pat_b]

Hello all,

I have been struggling to manipulate the lines in a line and clustered column chart in my work and largely attribute this to lacking an option of not sumarizing the line values (which in most field settings you can) and therefore i have been stuck setting up my data to sum. 

Anyways, my work requires target lines that vary according to which block/cycle of weeks i am observing. I have 2 seperate cycles (1 with 6 weeks and another with 4 weeks, both with different target values) which i have been able to distinguish using filters. However, i am required to now show both cycles at the same time on an x-axis and the line values become summarized, meanwhile i need them to be relevant specifically to each cycle. 

I hope this makes sense but if not the screen shots below should explain what i am aiming for. The first  is showing cycle 1, the second screenshot is of cycle 2, the third screenshot shows when cycle 1 and 2 are being aggregated but the line values are being summarized and then screen shot 4 indicates what i am aiming for. I have been dabling with various measures and approaches to fix this but would really appreciate some help.

Thanks,  Pat.

[v-jayw-msft]

Hi @pat_b ,

Could you please share some sample data to me if you don't have any Confidential Information?

I wonder if they are in the same table or different tables and what's the table structure like.

Maybe you can refer to the below table:

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pat_b]

Hi Jay, 

Thanks for your response.

Unfortunately this data is confidential, and for now would like to try and talk this out if possible. I can expand that i am basically filtering a large dataset with individual respondents by City, which can consist of one or many neighbourhoods of data, and have two different weekly blocks i need to trend the data  by (my x-axis) that have different targets. 

I have multiple tables for this work but i have also merged all the tables together using merged queries to try and have everything setup in one table like you have shown in your example. (I have triple checked that when the merge is performed that all the data lines up and is matched properly). Each individual response has week, cycle, targets, etc.

The only time i was able to get the target line to behave the way i need was setting the line value to a 'maximum' or 'minimum' condition rather than sum. When using sum it would add up all my entries and have values in the high thousands range, likely because my table is massive and filled with individual respondents which appears to add the same target value X amount of times.  Lets say in your example you had more entries with many duplicates of weeks in the data, how would you be able to have the target stay the same without adding them all together?

My approach of min or max also doesnt work fully because i have instances where certain cities have multiple neighbourhoods of data and then would only display data for either the min or max targets of one neighbourhood rather than all of them.

It does appear to be more of an issue with how the data is structured but i also have a hunch it could be due to a limitation of the lines on this type of chart.

I wonder if a measure could be used as a work around to have distinct values that can be filtered by a condition of cycle and display that on the line without summarizing them for every weeks entry. 

Sorry if this is confusing, the dataset has many factors within it and makes something as simple as a target line much more complex. Let me know if i can provide any other info to clarify or help find a solution

Thanks,

Pat