CODEWITHSUNDEEP
powerbidaxanalyticsdashboardpowerbi-desktop
Josue Barrantes
Josue Barrantes

Reputation: 49

Return the sum of the product of columns in different tables

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

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])))```

Upvotes: 0

Views: 1114

Answers (1)

msta42a
msta42a

Reputation: 3741

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;

Upvotes: 0

Related Questions