cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bn12pmebi
New Member

SUMX returning totals instead of row-level values

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?

2 REPLIES 2
yingyinr
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors