SUM based on measure is incorrect

[anonymous188]

Hi,

I have the following made-up tables:

Employee ID	Product Sold
111	Banana
112	Apple
112	Kiwi
113	Apple
113	Kiwi
113	Mango

Employee ID	Role ID
111	1
112	1
113	2
114	1
115	2

I'm trying to count the number of distinct products each employee has sold, but then multiply the number sold by 3 if their Role ID is 2 and by 1 if their Role ID is 1.  I'd like to create a chart within Power Bi with this information which would look like the following:

Employee ID	Bonus
111	1
112	2
113	9
114	0
115	0

I was able to calculate this based on the following solution:

Counts = 
var cnt = Count(Table1[Product Sold])
return cnt * if(MAX(Table2[Role ID]) = 2,3, 1) 

However, now when I attempt to calculate the sum of the resulting column, all the counts are multiplied by 3, rather than just the rows corresponding to employees with specific Role IDs.

The following is the incorrect sum:

Employee ID	Counts
111	1
112	2
113	9
Total	18

[Ashish_Mathur]

Hi @anonymous188,

Try this

1. Create a calculated column in the Role_ID table =if([Role ID]=1,1,3)

2. Create a relationship from the Employee ID column of the Data table to the Employee ID of the role_id table

3. In your visual, drag the Employee ID column from the role_ID column

4. Write the following measure

=DISTINCTCOUNT(Data[Product Sold])*MIN(role_id[Points])

Hope this helps.

[anonymous188]

Hi @Ashish_Mathur,

Thanks for the help. I found that I needed to calculate the measure as

Count = DISTINCTCOUNT(ProductsSold[Product Sold])*MAX(Employees[Points])

with MAX instead of MIN to get the right answer.

[Ashish_Mathur]

Hi @anonymous188,

If my reply helped, please mark it as Answer.

[Vvelarde]

@anonymous188

Hi, try with this measure;

Bonus2 =
SUMX (
    SUMMARIZE (
        Roles;
        Roles[Employee ID],
        "PSOLD"; IF (
            MAX ( Roles[Role ID] ) = 1,
            CALCULATE ( DISTINCTCOUNT ( 'Products Sold'[Product Sold] ) ),
            CALCULATE ( DISTINCTCOUNT ( 'Products Sold'[Product Sold] ) ) * 3
        )
    ),
    [PSOLD]
)

Regards

Victor

Lima - Peru

[parry2k]

Just answered on original thread 

although good idea to start seperate thread.

[anonymous188]

I tried changing MAX to SUM in the Count measure, but now the sum is the normal sum before multiplying the counts (i.e. a total of 6). For reference, the following is how the measure is now calculated:

Counts = 
var cnt = Count(ProductsSold[Product Sold])
return cnt * if(SUM(Employees[Role ID]) = 2, 3, 1)

[parry2k]

my bad, i just said sum without thinking, let me get back toyou

[parry2k · Accepted solution]

let's try this

add new column to get multiplier based on rold

m = 
var r = RELATED(Employee[Role ID]) 
return if(r=2,3,1) 

add a measure for count

Counts =  count(Sold[Product Sold])

add a measure for sum, multiple count with role multiplier 

product Sold Sum  = SUMX(Sold, [Counts]*Sold[m])

drop employee id and "product sold sum" measure in table visual and see if you get the result.