Forum Discussion
Totals using a formula are incorrect
Hello colleagues,
Please consider this table:
Column A is the number of underlying values in the row.
Column B is the numnber of distinct underlying values in the row.
Column C is the product of A and B (i.e. A * B).
If you do A * B across each row, you see the products are calculated correctly, including for the total row.
However if you sum each column, you'll see that A and B foot properly (totals equal the sum of the values above), but the A*B column does not sum. This is because of rows 2 and 3, which have more rows than they have distinct rows. Because the totals row is multiplying the sums of the individual columns A and B, the values of 21 in the second and third rows is being multiplied against the values of 5 and 271 in the first and fourth rows, creating a grand totals row that calculates across, but where the grand total for the A*B column does not equal the sum of the values.
Put another way, more simply, the grand total for A*B should be 73508. I want the totals to sum down, even though it means the grand totals math will not work across.
I've been wracking my brain on how to accomplish this.
5 Replies
- Shravan133
Super User
try creating a measure for the totals:
Total_Product = SUMX(YourTable, YourTable[ColumnA] * YourTable[ColumnB])
or
Seperate totals:
Total_A = SUM(YourTable[ColumnA])
Total_B = (similar for B)
- markmsc
Resolver I
Hello Uzi2019 , and thank you very much for your suggestion. I had looked at SUMX but understood it to mainly be useful for filtering the values in the column you want to sum.
Please permit me to build out my example a little more, and if you have time, perhaps you can point me to how SUMX can help here.
In my original post, I simplified the example. This one show exactly what I am trying to do. Here,
A is the DISTINCTCOUNT of underlying values in the row
B is the COUNT of underlying values in the row
A/B is the quotient of A divided by B
C is the SUM of another measure in the source table
C * A /B is, as the label suggests, is the product of the prior two columnsSo the DAX formula for C * A / B is:
Result = ROUND(SUM(TableX[Quantity]) * DISTINCTCOUNT(TableX[AttributeValue]) / COUNT(TableX[AttributeValue]) ,0)
As you can see in the new screenshot, this results in a grand total error in the last column where 0 + 100 - 100 - 223 is summed to -199, when the correct sum should be -223.
How would I use SUMX here, please? Thank you so much.
- AnonymousNot applicable
Hi,markmsc .I am glad to help you.
Has your problem been solved?
If you have found suitable solutions, please share them as it will help more users with similar problems.
For example, relevant code or links to articles.
Or you can mark the valid suggestions provided by other users as solutions.
Thank you very much for your understanding and support of Power BI.I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian