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

[watje255_ju]

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

[watje255_ju]

Working like a dream now, thank you very much @Jihwan_Kim !

[Jihwan_Kim]

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]

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]

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

[watje255_ju]

Hi @Jihwan_Kim , thanks again for your time. I would like the Dax measure to show the correct total for for each bucket ( the individual rows are correct), the matrix with the detail is correct, you can see Age 90+ days and the total has a NaN error in the table on the right, where I would like to see the 1009, and total of 7,477k? Much appreciated!

[Jihwan_Kim · Accepted solution]

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