Multiplying no. of level by level value

[eburke]

Ok I'm stumped, I feel like I should know this and it should be simple but for some reason I can't get this to work.  I have two tables, one has a list of clients and each has an assigned level.  The other table has the level number and the value of that level.  Example:

Table One:

Customer A - level 1

Customer B - Level 2

Customer C - Level 3

Customer D - Level 4

Table Two:

Level 1 = 1

Level 2 = 1

Level 3 = 3

Level 4 = 5

The two tables are linked through the level number (Levels 1-4)

I have a measure that counts the number of Customers in each level: 

No. of Clients = COUNT('Client Contracts (CC1936)'[Program Level])

This creates the following visual for me, (the rows are filter by the name of the clients' advisor, not included in the visual for privacy)

Now all i want to do is create a measure that calculates the total value of the clients across the rows.  So in excel it would just be the number of clients per level * the value of the level.  For the first row of the above visual this would be Level 2 (3*1)=3, level 3 (3*3)=9, Level 4 (1*5)=5.

I've tried several different measures but I'm not getting it right.  Any help would be appreciated, I'm just going around in circles.  Thanks,

[v-jiascu-msft · Accepted solution]

Hi @eburke,

There are two approaches. The measure could be the one below.

Measure = COUNT('Client Contracts (CC1936)'[Program Level]) * min(Table2[Value])

1. Use the Levels from table Client Contracts (CC1936) and change the Cross filter direction to Both.

2. Use the Levels from table Table2. 

You can try it out in this file.

Best Regards,

Dale

[v-jiascu-msft · Accepted solution]

Hi @eburke,

There are two approaches. The measure could be the one below.

Measure = COUNT('Client Contracts (CC1936)'[Program Level]) * min(Table2[Value])

1. Use the Levels from table Client Contracts (CC1936) and change the Cross filter direction to Both.

2. Use the Levels from table Table2. 

You can try it out in this file.

Best Regards,

Dale

[eburke]

Sorry just noticed that this gives the correct value in the table but the total is only calculated using the minimum level value as well.  I need the total to be the sum of all the values.  is there anyway i can get this to work?

[v-jiascu-msft · Accepted solution]

Hi @eburke,

Try this measure out please.

Measure 2 =
VAR summaried =
    SUMMARIZE (
        'Table1',
        'Table1'[Advisor],
        'Table2'[Level],
        'Table2'[Value],
        "sumValue", COUNT ( Table1[Customer] ) * 'table2'[value]
    )
RETURN
    IF (
        HASONEFILTER ( Table1[Advisor] ) && HASONEFILTER ( Table2[Level] ),
        COUNT ( Table1[Customer] ) * MIN ( Table2[Value] ),
        SUMX ( summaried, [sumValue] )
    )

Another simple way is bringing the value to the table 1 from table 2.

Valuefromtable2 = RELATED(Table2[Value] )

Best Regards,

Dale

[eburke]

Thank you that's worked perfectly.  Much appreciated.

[eburke]

Awesome that worked, thanks so much.