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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
twixy
Frequent Visitor

Additional operations on a column before running total

Hi,

I have a column of revenue over a few different years. I currently have a running total measure for the revenue over time but would like to apply a multplication to the column (to reflect currency exchange rate) before computing the running total. Is it possible to do so with a measure? I tried ammending my current running total code but was unsuccessful.

 

Data

 

YearRevenueCountry
2020$100A
2020$120B
2021$130B
2022$100B

 

Measure which is the Current Revenue ($) running total in Year returns a measure that when plotted gives 2020 - $220, 2021 - $350, 2022 - $450

 

I would l like it multiply the revenue by the correct exchange rate based on country and return the running total.

I.e. Exchange rate for country A = 0.5, Exchange rate for country B = 0.6.

Return  2020 = 0.6 x 100 + 0.8 x 120 = $156, 2021 = 156 +0.8 x 130 = $260, 2022 = 260 + 0.8 x 100 = $340

 

Thanks for any help you can provide!

 

 

Current running total code:

Current Revenue ($) running total in Year =
CALCULATE(
    SUM('Tracker'[Current Revenue ($)]),
    FILTER(
        ALLSELECTED('Tracker'[Year]),
        ISONORAFTER('Tracker'[Year], MAX('Tracker'[Year]), DESC)
1 ACCEPTED SOLUTION
Sahir_Maharaj
Super User
Super User

Hello @twixy,

 

Can you please try this approach:

Revenue_Running_Total_Converted = 
VAR ExchangeRate =
    SWITCH(
        SELECTEDVALUE('Tracker'[Country]),
        "A", 0.5,
        "B", 0.6,
        1
    )
RETURN
    CALCULATE(
        SUMX(
            'Tracker',
            'Tracker'[Revenue] * ExchangeRate
        ),
        FILTER(
            ALLSELECTED('Tracker'[Year]),
            ISONORAFTER('Tracker'[Year], MAX('Tracker'[Year]), DESC)
        )
    )

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

View solution in original post

2 REPLIES 2
Sahir_Maharaj
Super User
Super User

Hello @twixy,

 

Can you please try this approach:

Revenue_Running_Total_Converted = 
VAR ExchangeRate =
    SWITCH(
        SELECTEDVALUE('Tracker'[Country]),
        "A", 0.5,
        "B", 0.6,
        1
    )
RETURN
    CALCULATE(
        SUMX(
            'Tracker',
            'Tracker'[Revenue] * ExchangeRate
        ),
        FILTER(
            ALLSELECTED('Tracker'[Year]),
            ISONORAFTER('Tracker'[Year], MAX('Tracker'[Year]), DESC)
        )
    )

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

That worked, thanks @Sahir_Maharaj !

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.