Count values in another table

Question

Hello Team ,&nbsp;I have a below matrix table in my dashboard .Based on that I want show data in another table&nbsp;Below is matrix table&nbsp;----Client Name&nbsp;&nbsp; , Service line and revenue is main KPI .&nbsp;Service LineITHRLawClint name&nbsp;&nbsp;&nbsp;ABC504030XYZ40&nbsp;&nbsp;SAN20&nbsp;&nbsp;MMM101020&nbsp;explanation - In above table we can see Clint name – ABC and MMM , Revenue value present for All the (IT ,HR ,Law ) 3 &nbsp;service line – So we need to put this into below output -Revenue in 3 service line and count is = 2 &nbsp;(Ref - Because revenue is present in all the 3 service line thus the reason we take this in revenue in 3 service line &nbsp;)&nbsp;--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&nbsp;We take count as 1 for each client based on above logic .&nbsp;Expected output -below table&nbsp;&nbsp;Service line 1/2/3Count based on service lineRevenue in 1 Service line2Revenue in 2 Service line0Revenue in 3 Service line2&nbsp;Thank you .

anonymous · Accepted Answer

Hi&nbsp;SAPpowerbi&nbsp;,
&nbsp;
Here's my solutions.
Sample data:

&nbsp;
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&nbsp;situations.
Revenue in 1 Service line = CALCULATE(DISTINCTCOUNT('Table'[Clint name]),FILTER('Table',[Count]=1))+0Revenue in 2 Service line = CALCULATE(DISTINCTCOUNT('Table'[Clint name]),FILTER('Table',[Count]=2))+0Revenue 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.

&nbsp;
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.

&nbsp;
&nbsp;
Best Regards,
Stephen Tao
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;

Forum Discussion

Count values in another table

1 Reply

Related content

Count Distinct Values based on another column in Power Query

Counting nulls

Count distinct values in one column, grouped by another column

Count Occurrence of Column Values in Another Table

duplicate values count based on another column values

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