Need help with incorrect totals in table visual

[ERing]

I recently created a dashboard with sales funnel data. My end users requested that each KPI be calculated for the "Actual Period" and for a "Comparison Period". The Comparison Period is used to establish a baseline (historical) value.

In the bottom table I am using the Web Sessions from the Actual Period and using the Baseline conversion rates to create Projected Total Sales (=Projected Web Sales + Projected Phone Sales).

My issue is that the column totals (outlined in red) don't add up correctly, however the math for the Total line is correct.

So essentially I have individual rows that calculate correctly and a total row that calculates correctly, but columns that do not sum correctly.

I've been reading about the incorrect totals problem in Power BI, but I'm confused because my data is accurate when doing the math in the totals row, just not when summing the columns.

I could change the measures so that the columns would sum correctly, but then doing the math on the total row using the conversion rates would be incorrect.

I've replicated the issue using dummy data in the file below as I can't share my actual file due to confidentiality.

DUMMY DATA FILE 

[danextian · Accepted solution]

Because at the total level, what gets calculated is the total Web Session  * Web Sessions to Web Initiations Conversions Baseline. Your measure isn't telling DAX to evaluate each multiplication row by row and return the sum of those at the total row - it simply tells DAX to multiply A by B. If you want this to be evaluated row by row use SUMX.

SUMX (
    VALUES ( 'table'[Channel] ),
    [ Web Session] * [Web Sessions to Web Initiations Conversions Baseline]
)

Using VALUES will iterate only over each distinct Channel  Values instead of iterating the whole 'table' table. This will not make a difference though if the number of rows in 'table' is just the same as the number of distinct 'table'[Channel] values.

[v-nmadadi-msft]

Hi @ERing ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks and regards

[v-nmadadi-msft]

Hi @ERing,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-nmadadi-msft]

Hi @ERing,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[hnguy71]

Hi @ERing ,

You can fix this by using SUMX. So for all your measures that has the incorrect totals, just add SUMX on top of it. for example:

Projected Web Initiations = SUMX('Channel Mapping', [Web Sessions]*[Web Sessions to Web Initiations Conversion Baseline])

[ERing]

@hnguy71  Thanks for the your reply.

My confusion is because the total line is actually correct when you due the multiplication. Web Sessions (Column 1) * Web Session to Web Initiations Conversions Baseline (Column 2) = Projected Web Initiations (Column 3). You'll see that each row calculates correctly and the total row calculates correctly, yet somehow the sum of each row in column 3 does not equal the total.

So 2,618,651 * .170736 = 447,113.80  which is correct, yet summing each row in that column totals 450,425.71.

[danextian · Accepted solution]

Because at the total level, what gets calculated is the total Web Session  * Web Sessions to Web Initiations Conversions Baseline. Your measure isn't telling DAX to evaluate each multiplication row by row and return the sum of those at the total row - it simply tells DAX to multiply A by B. If you want this to be evaluated row by row use SUMX.

SUMX (
    VALUES ( 'table'[Channel] ),
    [ Web Session] * [Web Sessions to Web Initiations Conversions Baseline]
)

Using VALUES will iterate only over each distinct Channel  Values instead of iterating the whole 'table' table. This will not make a difference though if the number of rows in 'table' is just the same as the number of distinct 'table'[Channel] values.

[danextian · Accepted solution]

If I were in your shoes, I would explain to them that the difference arises from how multiplication works in math: the sum of individual products is not the same as the product of individual totals. From there, they can decide which value they would prefer to display in the total.

[ERing]

Thanks @danextian 

I understand I can create a measure that sums the column values for the total. 

The issue is that my end users will do the calculation at the total level (Web Sessions * Web Sessions to Web Initiations Conversion Baseline = Projected Web Initiations) and they will get 447,113.

I'm not sure how to explain why taking the sum of the Projected Web Initiations column (=450,425) does not equal the calculation using the values in the total row (447,113).

[hnguy71]

Hi @ERing 

I'm super confused...

Can you share an expected output in the form of a table or something? You're saying you're expecting 447 113.80, and that's what you have in your original measure, but then you're saying it's wrong... and you're also saying 450 425.71 is also incorrect. I'm not sure which one you're looking for?

[ERing]

@hnguy71 

You've described my problem exactly.

The situation is that my end users will look at this report and want to manually check the calculations. They will end up confused and come back to me for an explanation.

For example, lets say the end users want to verify the data in column 3 "Projected Web Initiations". They will go row by row and do the calculation =  (Web Sessions * Web Sessions to Web Initiations Conversion Baseline = Projected Web Initiations). The result of this calculation for each row are correct. NEXT, they will do the calculation using only the Total Row (2,168,751 * 17.0736% = 447,113) and this is also correct. The ISSUE is that the SUM of the Projected Web Initiations column DOES NOT equal the calcualtion if they use only the Total Row.

I can FORCE the Total for Projected Web Initiations to be the sum of that column, which is what I have done with the measure "Projected Web Initiations FIXED". That is all well and fine until an end user decides to do the calcuation on the Total Row (2,168,751 * 17.0736% = 447,113) and they get 447,113.80.

I hope this makes sense.

[Ashish_Mathur]

Hi,

The total row should not be a multiplication.  It should be an addition of the multiplied numbers i.e. 450,425.71.

[ERing]

Hi @Ashish_Mathur 

My trouble is how I explain this to my end users. They will do the simple math on the totals row by doing Web Sessions * Web Sessions to Web Initiations Conversion Baseline = Projected Web Initiations and they will get 447,113. The issue arises if they try to sum each row in the column Projected Web Initiations and they will get 450,425. They will be confused and looking for an explanation.