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
Jihwan_Kim's avatar
Jihwan_Kim
Super User
3 years ago

Hi,

I am not sure how your desired visualization looks like, but please try writing two measures like below and check if it suits your requirement.

 

Test USDRR =
VAR gggest =
    SUMX ( 'Policy (measures)', [Gross] )
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
        ),
        [@AmountinSett] * [@ToUSDRates]
    )
RETURN
    AggPremum

 

Test USDRR Total Fix measure =
SUMX ( 'Policy (measures)', [Test USDRR] )
  • 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]
)