adding total line to chart (>50 categories)

[wynhodgkiss]

Hi all,

I have one calculated measure that is across 7 categories each with 5-10 sub-categories.

What I'm trying to produce is a line chart showing a line per sub-category with a single select slicer containing the 7 categories. 

All good so far.

Now, what I'd like to add is:

1. a line to represent the total of all the sub-categories within the selected category

2. a line to represent the average of all categories

3. a line to represent the average of the non-selected categories

Is there a way to achieve this without having to write >50 new measures, one for each sub-category? I'm thinking a table with all the categories and sub-categories and a row for each of the above, related to the dimension table (for the slicer) but not related to the main data table. But after that I'm struggling with the DAX for the above 3 measures and any help would be appreciated.

[v-zhangti]

Hi, @wynhodgkiss 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

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

[wynhodgkiss]

Hi,

Image 1 is the existing chart with Category 1 in the slicer (Region) and Category 2 in the Legend (ICB).

Image 2 is the total line for the selected Region

Image 3 is the total line for the non-selected Regions 

1

1

2

2

3

3

The measure used in all 3 charts is a simple division [mg per 100k pop]. The Region slicer is from a related dimension table. The X axis is months from a custom dates table.

What I want to achieve is all 3 images in the same chart, ideally without having to create a new measure for every possible Category 2 value.

Hope that helps explain better what I'm after.