Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Table Total Not Summing Correctly

Hello,

I know this is a very common error and I am not sure why I can't seem to wrap my head around such a simple error. Please see the below image as the last column is not summing correctly.

 

 

Now my measures are as follows, for the column Total Borrowing:

Total Borrowing =

Var Oustanding =

CALCULATE(SUM(Table'[Amount]),

Table'[TransactionType] = "Outstanding")

 

Var QBOutstanding =

CALCULATE(SUM(Table'[Amount]),

Table'[TransactionType] = "QBOutstanding")

Return

Outstanding + QBOutstanding

 

Now the Bank Percentage of Fund measure is:

BankPercentage =

Var SelectedBank = SelectedValue(Bank List'[Bank Name])

Var BankPercentage = Calculate(MAX(Bank Data'[Bank %]),

                Bank Data'[Bank] = SelectedBank

 

Then for the final column the measure is:

Total Borrowing * Bank Percentage

 

Now the thing is that it is showing the correct results by row, but it isn't showing the correct total at the bottom of the column. I understand that it is taking the max percentage, hence why it is multiplying the 100% bank rate to the borrowing which provides the 1.2 Billion number. My thing is how do I get to multiply by row and then show that.

 

 

If I can be more clear, please just let me know.

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

     

    Please try this:

    First of all, I create a set of sample:

    Then add 2 measures:

    Measure1 = MAX('Table'[percentage])
    Measure 2 = SUM('Table'[Total Sales])

    Then I create measure3:

    Measure 3 = SUMX('Table',[Measure1]*[Measure 2])

    The result is as follow, the total could be correct:

    If the above one can't help you, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

    How to provide sample data in the Power BI Forum

    How to Get Your Question Answered Quickly

    And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Anonymous 

     

    Please try this:

    First of all, I create a set of sample:

    Then add 2 measures:

    Measure1 = MAX('Table'[percentage])
    Measure 2 = SUM('Table'[Total Sales])

    Then I create measure3:

    Measure 3 = SUMX('Table',[Measure1]*[Measure 2])

    The result is as follow, the total could be correct:

    If the above one can't help you, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

    How to provide sample data in the Power BI Forum

    How to Get Your Question Answered Quickly

    And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hello,

      This worked actually! Thank you so much!