Forum Discussion

smjoshi's avatar
smjoshi
Frequent Visitor
4 years ago
Solved

Need help in this matrix visual

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 IDCase ID
40001000
40001001
50002001
50002002
50002003
60003001
60003002
70004001
70004002
70004003
70004005
80005001
80005002

 

Table 2

Bundle IDCount of Case ID per bundle
40002
50003
60002
70004
80002

 

Final output table 3

Count of Case ID per bundle from table 2 header234so on
Count of bundle ID with  count of caseIDS values311 

 

Thanks

Smita

Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      smjoshi
      Frequent Visitor
      4 years ago

      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

       

  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    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.