DAX Measure Total Wrong

Question

Hi All,I'm struggling to get the&nbsp; grand total right, its computing fine on row by row basis but the grand total show exponential large amount.&nbsp;The last reutn statement will check if the row contains unique value and I need to add somehting in there to tell sum the columns after the conversion to currency is done.&nbsp;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

IF(HASONEVALUE('Pol (measures)'[Policy ID]), AggPremum, ???? ( How to Get 
  Total Amount ) &nbsp;

ericshahi · Answer

Greg_Deckler&nbsp;&nbsp;Jihwan_Kim&nbsp;&nbsp;PaulDBrown&nbsp;amitchandak&nbsp;

greg_deckler · Answer

EricShahi&nbsp;First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742eThis looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907Also: https://youtu.be/uXRriTN0cfYAnd: https://youtu.be/n4TYhF2ARe8

jihwan_kim · Answer

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.
&nbsp;
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

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

ericshahi · Answer

Hi Greg,I read thorugh your documnets around the issue on the measures totals, I tried to used your techinique but still no luck.Test USDRR =&nbsp;VAR gggest =&nbsp;&nbsp;&nbsp;&nbsp;SUMX (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Pol (measures) (measures)',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Gross Written Premium Our Share]&nbsp;&nbsp;&nbsp;&nbsp;)VAR AggPremum =&nbsp;&nbsp;&nbsp;&nbsp;SUMX(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ADDCOLUMNS (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUMMARIZE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Pol (measures)',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Pol (measures)'[Reporting Currency],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Pol (measures)'[Policy ID]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"@ToUSDRates",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IF (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ISBLANK (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LOOKUPVALUE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Exchange Rate'[ExchangeRate Factor],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Exchange Rate'[Currency From], 'Pol (measures)'[Reporting Currency],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Exchange Rate'[Currency To], "USD"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LOOKUPVALUE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Exchange Rate'[ExchangeRate Factor],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Exchange Rate'[Currency From], 'Pol (measures)'[Reporting Currency],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Exchange Rate'[Currency To], "USD"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"@AmountinSett", gggest&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IF (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;HASONEFILTER ( 'Pol (measures)' [Policy ID]&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[@AmountinSett] * [@ToUSDRates],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUMX (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Pol (measures)',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[@AmountinSett] &gt; 10000000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[@AmountinSett] * [@ToUSDRates]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;)RETURN&nbsp;&nbsp;&nbsp;&nbsp;AggPremum

ericshahi · Answer

Hi Jihwan,&nbsp;I created an additional measure&nbsp;Test USDRR Total Fix measure =
SUMX ( 'Policy (measures)', [Test USDRR] )&nbsp;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] &gt; 10000000
                ),
                [@AmountinSett] * [@ToUSDRates]
            )
        )
    )
RETURN
    AggPremum

Forum Discussion

Related content

Total measures show wrong total when report is published

Wrong Total

% of total measure

Total of Measure

DAX Measure, Total is wrong