Forum Discussion

markmsc's avatar
markmsc
Icon for Resolver I rankResolver I
1 year ago

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

  • 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)

     

  • Uzi2019's avatar
    Uzi2019
    Icon for Community Champion rankCommunity Champion

    Hi markmsc 

     

    to correct the Grand total you have to use different function then Sum. Use SUMX() funtion. which is iterative function. different from SUM. 
    Measure= SUMX( 'Table Name', Sum(ColumnA)*SUM(ColumnB).

     

    I hope this might help you!

     

     

    • markmsc's avatar
      markmsc
      Icon for Resolver I rankResolver 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 columns

       

      So 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.

       

      • Uzi2019's avatar
        Uzi2019
        Icon for Community Champion rankCommunity Champion

        Hi markmsc 

         

        Try this

        Var A=DISTINCTCOUNT(TableX[AttributeValue])
        var B= COUNT(TableX[AttributeValue])
        Var F= Divide(A,B,0)

        Return  SUMX( TableName, SUM(TableX[Quantity]) * F)


         

        I hope I answered your question!

         

  • Anonymous's avatar
    Anonymous
    Not 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