Set Analysis involving a condition having a variable that changes per dimension

[sjmghames]

Hi,

I have a set of data , say invoices with 3 columns

Table:

Invoice Key    Hospital Name    Invoice Items Count

1                      A                        2

2                      A                        4

3                      A                        4

4                      B                        3

5                      B                        5

I need to build a table which gives the following:

Visualization:

Hospital Name           Max Invoice Items Count per Invoice            Invoice Count having max invoice items count

A                                 4                                                                      2

B                                 5                                                                      1

Column 2 ( i.e. Max Invoice Items Count per Invoice) formula: Max (Invoice Items Count)

How can I get column 3 (Invoice Count having max invoice items count) formula? I need to know how many invoices have the max invoice items count per hospital.

I tried : count({<[Invoice Items Count] = {"$(=Max([Invoice Items Count]))"}>} Invoice Key) but it didnt work.

Any help??

[sjmghames]

Can anyone help in this?

[sunny_talwar]

May be this

Dimension

Hospital Name

Expression

Max([Invoice Items Count])

Count(Aggr(If([Invoice Items Count] = Max(TOTAL <[Hospital Name]> [Invoice Items Count]), [Invoice Items Count]), [Hospital Name], [Invoice Items Count]))