Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
EricShahi
Helper IV
Helper IV

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 contains unique value and I need to add somehting in there to tell sum the columns after the conversion to currency is done.

 

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 ) 

 

6 REPLIES 6
Jihwan_Kim
Super User
Super User

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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.


EricShahi_0-1667896989911.png


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



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

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


EricShahi
Helper IV
Helper IV

@EricShahi First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This 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/63376

Also, 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/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.