SUMX returning totals instead of row-level values

[bn12pmebi]

I’m setting up a customer score based on scores from 8 sources (queries).   When I started I setup the formula for scoring each of the 8 data points in the powerquery , then I was told by a colleague that my “implicit” formulas in the queries wouldn’t work in visualizations, and I had to create external Measures to get the calculations I want. 

I didn’t want to recreate all those formulas, so I tried using SUMX to “pull in” the row-level values from the implicit formulas in the query so they show in a table or matrix display.  For example:

Sumx Score 3 = SUMX('query3',[Query3 iScore])

This worked perfectly for 5 of my data points, but for 3 the SUMX returns a grand total of the implicit formula column, not the row-level values, like this:

Customer Name               Sumx Score 1      Sumx Score 2      Sumx Score 3      Sumx Score 4

A                                            10                           0                             0                       65          

B                                             0                            10                           0                        65          

C                                             5                            10                           0                        65

You can see in Score 4 that I got the total and not the row value for the implicit formula column.  Also, when this happens I get multiple rows for the same Customer Name instead of one row per-Customer Name. Note that "show value as" is set to “No Calculation.”

How do I figure out why this is working fine for 5 calculations, but shows totals for 3 calculations?

Is there a better way I can create a Measure to re-use the formula calculations (calculated columns) in the queries instead of redoing the calculations as external measures from scratch?

[v-yiruan-msft]

Hi @bn12pmebi ,

It's a little difficult to find the cause of problem and get the solution without any data and the left 3 calculations with wrong total values. Could you please share some involved data in those 3 calculations(exclude sensitive data) and their formula with us in order to make troubleshooting? By the way, those 3 calculations are measures or calculated column? Thank you.

Best Regards

[amitchandak]

@bn12pmebi , Make sure Query4 has an active join with the customer. if the join is inactive, check if this is because of bi-directional joins