Forum Discussion

JohnLopezHZ's avatar
JohnLopezHZ
New Member
3 years ago
Solved

Grand Total not calculating correctly - Calculated Column

I have a table where one of the columns, called AR Gross Totals, is a calculated column. My intent with this column is to get the totals of each category and give a total if the category as a whole is greater than 0. I believe I have the correct formula for this but the grand total is not correct at the bottom of the calculated column even though all the row values are correct.

Here is the formula: AR Gross Totals = CALCULATE ( SUM ( PaymentsByType_AllStores[PaymentAmt] ), FILTER ( VALUES ( PaymentCategories[Payment Category] ), CALCULATE ( SUM ( PaymentsByType_AllStores[PaymentAmt] ) ) > 0 ) )

 

Table Names are PaymentsByType_AllStores and PaymentCategories

Columns for PaymentsByType are AR Gross Totals, PaymentAmt, and PaymentTypeID

Columns for PaymentCategories are PaymentTypeCode and Payment Category.

 

The two tables are in a one to many relationship PaymentCategories -> PaymentsByType_AllStores where PaymentTypeCode = PaymentTypeID. 

 

I am using Direct Query btw

 

 

Can someone help me figure out why the Grand Total isn't correct?

Need Help
Tutorial Requests
  • Ahmedx's avatar
    Ahmedx
    3 years ago

    write another measure like this

    AR Gross Totals Fixed = SUMX(
       SUMMARIZE('PaymentsByType_AllStores',
          'PaymentsByType_AllStores'[Location],
           'PaymentsByType_AllStores'[Loc Key],
           'PaymentsByType_AllStores' [Tr Source]) ,

         [AR Gross Totals] )

2 Replies

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    3 years ago

    write another measure like this

    AR Gross Totals Fixed = SUMX(
       SUMMARIZE('PaymentsByType_AllStores',
          'PaymentsByType_AllStores'[Location],
           'PaymentsByType_AllStores'[Loc Key],
           'PaymentsByType_AllStores' [Tr Source]) ,

         [AR Gross Totals] )