How do you show a count by category when the category is not in the visual.

[tomperro]

How do you show a count by category when the category is not in the visual.

I have two related tables, Category and Tests. 
I have a measure that counts how many tests per category, this works when the category is in the table visual, however, when I remove the category from the visual, obviosly the count by categories does not work. 

Is there a way to create a measure that will count the number of tests and group by category without the category in the visual?
I have another table visual that shows the categories and a score, which is an aggregated score of multiple test numbers and I need a seperate table that shows the test number and that individual aggregated score for that test number for each category. 

Aggregated Score (measure) is a combination of other counts.

Example
 

Category	Aggregated Score (measure)
A	123
B	345
C	456

Test	Category
1	A
2	A
3	B
4	B
5	C
6	C

Current Visual

Category	Score (sum of all test scores)
A	8
B	2
C	4

Required Visual

Test	Aggregated Score (measure)
Test 1	5
Test 2	3
Test 3	1

[wardy912 · Accepted solution]

Hi @tomperro 

 Assuming your tables are split as you've shown, and are related, try this

Aggregated Score per Test = 
CALCULATE(
    [Aggregated Score],
    FILTER(
        ALL('Category'),
        'Category'[Category] = RELATED('Tests'[Category])
    )
)

I hope this helps, please give a thumbs up and mark as solved if it does, thanks!

[wardy912]

Try this:

Aggregated Score per Test =
CALCULATE(
    [Aggregated Score],
    FILTER(
        ALL('Category'),
        'Category'[Category] = LOOKUPVALUE('Tests'[Category], 'Tests'[Test], SELECTEDVALUE('Tests'[Test]))
    )
)

[wardy912 · Accepted solution]

Hi @tomperro 

 Assuming your tables are split as you've shown, and are related, try this

Aggregated Score per Test = 
CALCULATE(
    [Aggregated Score],
    FILTER(
        ALL('Category'),
        'Category'[Category] = RELATED('Tests'[Category])
    )
)

I hope this helps, please give a thumbs up and mark as solved if it does, thanks!

[tomperro]

@wardy912 , this looks like a solution but I am getting an error: cannot find name 'Tests'[Category]

[wardy912]

Hi @tomperro 

 You need to use the measure as a template, add your table/column names.

[tomperro · Accepted solution]

Changed the measure:

Test Composite Score =

    VAR CurrentCategory = SELECTEDVALUE(Tests[Test Category])

    RETURN

    CALCULATE(

        [Composite Score],

        Filter(

            ALL(Category),

            Category[Category] = CurrentCategory

        )

    )

[tomperro]

@wardy912 I have done that and that is where i am getting the error