How to do a sum product but over multiple columns

[aray99]

Hello Power BI Community,
I have found many methods to do a sumproduct in powerbi but my use case is a bit different.

EDIT:
-----------
Here's a sample of my raw data in text. We can call this table A.

Category	TP	Range	No_Accts	Loss Amt
NR	-6	0	885743	194
NR	-6	1-599	789566	1986
NR	-6	600-649	853296	1052
NR	-5	650-699	1236840	1071
NR	-4	700-749	262685	702
NR	-3	750-799	1347745	54
NR	-2	>=800	661154	285
NR	-1	0	702984	427
NR	0	1-599	315046	1984
NR	1	600-649	877738	155
R	2	650-699	1480603	471
R	3	700-749	48172	836
R	4	750-799	1480934	1510
R	5	>=800	1285418	974
R	6	0	878502	1447
R	7	1-599	1384862	1823
R	8	600-649	544773	667
R	9	650-699	869567	1819
R	10	700-749	1485262	1554
R	11	750-799	89739	193
R	12	>=800	160044	608
R	13		156450	1990

Here's a matrix I made in powerbi which calculates the sum of num_of_acct by range for R and then calculates percentage of total of the sum. The same is done for NR. This can be table B.

Row Labels	Sum of Num_of_Accts	%age
0	878502	9%
(blank)	156450	2%
1-599	1384862	14%
600-649	544773	6%
650-699	2350170	24%
700-749	1533434	16%
750-799	1570673	16%
>=800	1445462	15%
Grand total	9864326	100%

Here's a matrix I made in powerbi by making two measures to calculate the same percentages above and combining the R and NR data: This can be table C.

Range	%age R	%age NR
0	9%	20%
(blank)	2%	0%
1-599	14%	14%
600-649	6%	22%
650-699	24%	16%
700-749	16%	3%
750-799	16%	17%
>=800	15%	8%

Here's a matrix I made for loss_amt/num_of_acct. Columns are time period (tp) and rows are Category. This can be table D.

Range	-6	-5	-4	-3	-2	-1	0	1	2	3	4	5	6
0	0.000	-	-	-	-	0.001	-	-	-	-	-	-	0.002
>=800	-	-	-	-	0.000	-	-	-	-	-	-	0.001	-
1-599	0.003	-	-	-	-	-	0.006	-	-	-	-	-	-
600-649	0.001	-	-	-	-	-	-	0.000	-	-	-	-	-
650-699	-	0.001	-	-	-	-	-	-	0.000	-	-	-	-
700-749	-	-	0.003	-	-	-	-	-	-	0.017	-	-	-
750-799	-	-	-	0.000	-	-	-	-	-	-	0.001	-	-
(blank)	-	-	-	-	-	-	-	-	-	-	-	-	-

As output, I want the sumproduct between the values in each column with the %age R values and %age NR.

So starting in time period 0, don't need the ones before that. The result is the below. I made up the dummy data numbers so they're weird but the real data would have numbers. This can be table E.

Category	0	1	2	3	4	5	6	7	8	9
R	0.000347789	2.7533E-05	0	0	1563.530407	6.74823E-05	0	0	0	0
NR	0.0009	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000

I need help to make the above table. 

To make the first column, first row in table E, we first go to table D then take all the values from the column 0 (excluding the values corresponding to blank and 0 in the Range rows). Then we go to table C, we take all the values from the column %age R (excluding the values corresponding to blank and 0 in the Range rows). Now we get the sumproduct of all these values and put it in first row and first column of table E.
For first column and second row of table E, we first go to table D then take all the values from the column 0 (excluding the values corresponding to blank and 0 in the Range rows). Then we go to table C, we take all the values from the column %age NR (excluding the values corresponding to blank and 0 in the Range rows). Now we get the sumproduct of all these values and put it in second row and first column of table E.
To make the second column, first row in table E, we first go to table D then take all the values from the column 1 (excluding the values corresponding to blank and 0 in the Range rows). Then we go to table C, we take all the values from the column %age R (excluding the values corresponding to blank and 0 in the Range rows). Now we get the sumproduct of all these values and put it in first row and second column of table E.

For second column and first row of table E, we first go to table D then take all the values from the column 1 (excluding the values corresponding to blank and 0 in the Range rows). Then we go to table C, we take all the values from the column %age NR (excluding the values corresponding to blank and 0 in the Range rows). Now we get the sumproduct of all these values and put it in first row and second column of table E.

Please let me know if I can provide any further information.

[Greg_Deckler]

@aray99 This is the general formula for SUMPRODUCT in DAX:

	
SUMPRODUCT = 
    VAR __Table = 
        ADDCOLUMNS(
            'Table1',
            "Value",[Value1] * RELATED(Table2[Value2])
        )
RETURN
    SUMX(__Table,[Value])

It comes from here: S Excel to DAX Translation - Microsoft Fabric Community

Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[aray99]

Hi Greg,
I remade the post and hoping it's better now.
Pls let me know if I can provide any further information.