Forum Discussion

EricShahi's avatar
EricShahi
Helper IV
3 years ago

DAX Measure Total Wrong

Hi All, I'm struggling to get the  grand total right, its computing fine on row by row basis but the grand total show exponential large amount.  The last reutn statement will check if the row...
Need Help
EricShahi's avatar
EricShahi
Helper IV
3 years ago

Hi Jihwan,

 

I created an additional measure 

Test USDRR Total Fix measure =
SUMX ( 'Policy (measures)', [Test USDRR] )

 but this is causing perfomrace issue , and sometimes prompting me to add less columns values/ filter.





Test USDRR = 
VAR gggest =
    SUMX (
        'Pol (measures) (measures)',
        [Gross Written Premium Our Share]
    )
VAR AggPremum =
    SUMX( 
        ADDCOLUMNS (
            SUMMARIZE (
                'Pol (measures)',
                'Pol (measures)'[Reporting Currency],
                'Pol (measures)'[Policy ID]
            ),
            "@ToUSDRates",
                IF (
                    ISBLANK (
                        LOOKUPVALUE (
                            'Exchange Rate'[ExchangeRate Factor],
                            'Exchange Rate'[Currency From], 'Pol (measures)'[Reporting Currency],
                            'Exchange Rate'[Currency To], "USD"
                        )
                    ),
                    1,
                    LOOKUPVALUE (
                        'Exchange Rate'[ExchangeRate Factor],
                        'Exchange Rate'[Currency From], 'Pol (measures)'[Reporting Currency],
                        'Exchange Rate'[Currency To], "USD"
                    )
                ),
            "@AmountinSett", gggest
        ),
        IF (
            HASONEFILTER ( 'Pol (measures)' [Policy ID]  ),
            [@AmountinSett] * [@ToUSDRates],
            SUMX (
                FILTER (
                    'Pol (measures)',
                    [@AmountinSett] > 10000000
                ),
                [@AmountinSett] * [@ToUSDRates]
            )
        )
    )
RETURN
    AggPremum



Jihwan_Kim's avatar
Jihwan_Kim
Super User
3 years ago

Hi,

Thank you for your feedback.

I am not sure how your Policy(measures) table looks like, but please try writing DAX measure something like below. Or, please share your sample pbix file's link, and then I can try to look into it to come up with a more accurate solution.

 

Test USDRR Total Fix measure =
SUMX (
    DISTINCT ( 'Policy (measures)'[Your desired column that is in the visualization] ),
    [Test USDRR]
)