Count values in another table

[SAPpowerbi]

Hello Team ,

I have a below matrix table in my dashboard .Based on that I want show data in another table

Below is matrix table

----Client Name   , Service line and revenue is main KPI .

Service Line	IT	HR	Law
Clint name
ABC	50	40	30
XYZ	40
SAN	20
MMM	10	10	20

explanation - In above table we can see Clint name – ABC and MMM , Revenue value present for All the (IT ,HR ,Law ) 3  service line – So we need to put this into below output -Revenue in 3 service line and count is = 2  

(Ref - Because revenue is present in all the 3 service line thus the reason we take this in revenue in 3 service line  )

--Same for Clint XYZ and SAN is present in 1 service line like IT – so we need to put this into below Revenue in 1 service line and count is = 2

We take count as 1 for each client based on above logic .

Expected output -below table

Service line 1/2/3	Count based on service line
Revenue in 1 Service line	2
Revenue in 2 Service line	0
Revenue in 3 Service line	2

Thank you .

[Anonymous · Accepted solution]

Hi @SAPpowerbi ,

Here's my solutions.

Sample data:

1.Create a count measure to count the service line.

Count = CALCULATE(COUNT('Table'[Service Line]),ALLEXCEPT('Table','Table'[Clint name]))

2.Create another three measures to count three situations.

Revenue in 1 Service line = CALCULATE(DISTINCTCOUNT('Table'[Clint name]),FILTER('Table',[Count]=1))+0

Revenue in 2 Service line = CALCULATE(DISTINCTCOUNT('Table'[Clint name]),FILTER('Table',[Count]=2))+0

Revenue in 3 Service line = CALCULATE(DISTINCTCOUNT('Table'[Clint name]),FILTER('Table',[Count]=3))+0

3.Put three measures into a matrix visual, and then switch values to rows.

Another solution:

1.Create a table by entering data.

2.Create a measure with SWITCH function to count.

Count based on service line =
SWITCH (
    MAX ( 'Table (2)'[Service line 1/2/3] ),
    "Revenue in 1 Service line",
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[Clint name] ),
            FILTER ( 'Table', [Count] = 1 )
        ) + 0,
    "Revenue in 2 Service line",
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[Clint name] ),
            FILTER ( 'Table', [Count] = 2 )
        ) + 0,
    "Revenue in 3 Service line",
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[Clint name] ),
            FILTER ( 'Table', [Count] = 3 )
        ) + 0
)

3.Put the column and the meaesure into a table visual.

Best Regards,

Stephen Tao

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