Forum Discussion

watje255_ju's avatar
watje255_ju
Icon for Helper III rankHelper III
4 years ago
Solved

SUMX not totalling correctly (as expected)/ causing error in matrix?

Hello, 

I have had a good read of the forums, but I cannot find an answer that works for me so looking for some more help please.

 

The SUMX measure is not giving the total I would expect (the sum of all the rows), which is 7,477,673 (see pivot below). I think this is throwing out the NaN values in the matrix.

 

The measure and matrix is working perfectly for company 3 however, does anyone know why? PBIX link

below https://1drv.ms/u/s!As8wMr9_bgGrgWHiaY16y4XdpEnn?e=wBkO8L

 

This v-cazheng-msft is a follow on of your help tamerj1   

 

Thanks for your time

 

 

Test Group NZD Balances =

VAR FXTranslationDate = // set the FX translation date to the slicer reporting date
MAX ( DimDate[DateKey] )
VAR SelectedCurrencyKey = // find the currency key for the particular invoice
MAX ( FactAccountsReceivable[Currency Key] )
VAR LookedExchangeRate = // filter the currency table for the reporting date and the invoice currency
CALCULATE (
MAX ( FactExchangeRate[Exchange Rate] ),
FILTER (
FactExchangeRate,
FactExchangeRate[Valid From] = FXTranslationDate
&& FactExchangeRate[To Currency Key] = SelectedCurrencyKey
)
)
VAR ConvertFXInvoices = //exclude NZD balances from the calculation
CALCULATE ( SUMX (
FactAccountsReceivable,
FactAccountsReceivable[Amount Currency] / LookedExchangeRate),
FILTER (FactAccountsReceivable,
FactAccountsReceivable[Currency Key] <>4))

VAR Co6NZDInvoices = // filter for NZD invoices which don't need an FX translation
CALCULATE ( SUM ( FactAccountsReceivable[Amount Currency]) ,
FILTER ( FactAccountsReceivable , FactAccountsReceivable[Currency Key] =4))

Return
ConvertFXInvoices + Co6NZDInvoices
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Thank you for your feedback.

    Could you please check the below picture and the attached pbix file?

     

     

    Measure total fix: = 
SUMX (
    SUMMARIZE (
        FactAccountsReceivable,
        FactAccountsReceivable[Voucher],
        FactAccountsReceivable[Customer Key]
    ),
    [Test Group NZD Balances]
)

     

    Amount Per Bucket = 
CALCULATE (
    [Measure total fix:],
    FILTER (
        VALUES ( FactAccountsReceivable[Voucher] ),
        VAR FromDays =
            MIN ( 'Aging Groups ATB'[From] )
        VAR ToDays =
            MAX ( 'Aging Groups ATB'[To] )
        RETURN
            [Days Over 2] > FromDays
                && [Days Over 2] <= ToDays
    )
)

6 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Please try the below measure.

    Sorry that I could not fully understand your measure, however, the cause of not providing correct total might be solved by writing the below measure additionally.

     

     

    Measure total fix: = 
SUMX( VALUES( FactAccountsReceivable[Voucher]), [Test Group NZD Balances] )
    • watje255_ju's avatar
      watje255_ju
      Icon for Helper III rankHelper III
      4 years ago

      Hi Jihwan_Kim 

       

      Thanks so much for your reply, that works great, do you know why I am getting the NaN error for the 90+ day bucket when the just looking at the bucket level? Thanks again! 

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        4 years ago

        Hi,

        Thank you for your message.

        It is quite difficult for me to check without understanding what your DAX measures are looking for.

        Could you please try the below measure and please let me know if it provides the correct outcome or not.

         

        Amount Per Bucket =
SUMX (
    VALUES ( 'Aging Groups ATB'[Bucket] ),
    CALCULATE (
        [Measure total fix:],
        FILTER (
            VALUES ( FactAccountsReceivable[Voucher] ),
            VAR FromDays =
                MIN ( 'Aging Groups ATB'[From] )
            VAR ToDays =
                MAX ( 'Aging Groups ATB'[To] )
            RETURN
                [Days Over 2] > FromDays
                    && [Days Over 2] <= ToDays
        )
    )
)