Need help in this matrix visual

[smjoshi]

Hello 

I have Table 1 as input data. Table thrid is final outut table. I'm able to dervie till table 2 only. Need help how to dervie table 3 in simple way 

Table 1

Bundle ID	Case ID
4000	1000
4000	1001
5000	2001
5000	2002
5000	2003
6000	3001
6000	3002
7000	4001
7000	4002
7000	4003
7000	4005
8000	5001
8000	5002

Table 2

Bundle ID	Count of Case ID per bundle
4000	2
5000	3
6000	2
7000	4
8000	2

Final output table 3

Count of Case ID per bundle from table 2 header	2	3	4	so on
Count of bundle ID with  count of caseIDS values	3	1	1

Thanks

Smita

[v-zhangti · Accepted solution]

Hi, @smjoshi 

You can try the following methods.

Calculated column:

Count of Case ID per bundle =
CALCULATE (
    COUNT ( 'Table'[Bundle ID] ),
    FILTER ( 'Table', [Bundle ID] = EARLIER ( 'Table'[Bundle ID] ) )
)

Count of bundle ID with  count of caseIDS =
DIVIDE (
    CALCULATE (
        COUNT ( 'Table'[Count of Case ID per bundle] ),
        FILTER (
            'Table',
            [Count of Case ID per bundle] = EARLIER ( 'Table'[Count of Case ID per bundle] )
        )
    ),
    [Count of Case ID per bundle]
)

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.

[Anonymous]

HI @smjoshi 

You need to have table two and once you have it use matrix and take "Count of Case ID per bundle" as column and again "Count of Case ID per bundle" in values section. Then click on the dropdown in values section and select count.

[smjoshi]

Hello, Do I have to create table 2 as teampory table in my data model? OR  Can I do it without deriving table2 as temp table everything on the fly

Thanks