Return the sum of the product of columns in different tables

Question

I have 3 tables in PowerBI with columns below, for different systems:

• Platform 1: [Date, Sub_System, ticket_number]
• Platform 2: [Date,Sub_System, ticket_number]
• Standard_time_by_system: [System, Sub_system, multiplier]

I need to group by date the product of count of ticket_number and the multiplier by subsystem, which then gives the total by system

The error I'm having is that the total by system returns the total count of tickets * sum of multiplier by system, not the sum of the iteration of ticket_number * multiplier.

Example:

Platform 2

Date	Sub_system	ticket_number
1/1/2022	Sub_system 1	100001
1/2/2022	Sub_system 2	100003

Platform 1

Date	Sub_system	ticket_number
1/1/2022	Sub_system 1	100004
1/2/2022	Sub_system 2	100005

standard_time_by_system

System	Sub_system	multiplier
System 1	Sub system 1	5
System 1	Sub system 2	3
System 2	Sub system 3	7
System 2	Sub system 4	6

I need

System	Sub system	count(ticket_number)*multiplier
System 1	Sub system 1	10
System 1	Sub system 2	6
System 1 total		16

I'm getting

System	Sub system	count(ticket_number)*multiplier
System 1	Sub system 1	10
System 1	Sub system 2	6
System 1 total		32

Hope the example works a bit, I'm doing something like the below but it returns the wrong result again first adding the multipliers and not doin the iteration

        ISFILTERED('Standard_time_by_system'[System]),
            (COUNTA('Platform 1'[sub_system])+COUNTA('Platform 1'[sub_system]))*SUM('Standard_time_by_system'[multiplier]),
            SUMX('Standard_time_by_system'[System],Standard_time_by_system[multiplier]*COUNTA('Role Management Tracker'[System])))```

Answer 1

Your Total is calculated by the second part of the IF statement, where you use additional table 'Role Management Tracker'[System] (not described);

SUMX('Standard_time_by_system'[System],Standard_time_by_system[multiplier]*COUNTA('Role Management Tracker'[System]))

you should check this part;