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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Zosy
Helper II
Helper II

Sum of grouped invoice lines

Hi,

I have the below data. a list of detailed invoice product lines. Each invoice has an estimated total amount which appears on several lines when the invoice has more than 1 product.

InvoiceProductPriceEstimated Invoice totalCustomerDetails
1A100500CondAmazon
1B200500CondAmazon
1N3500CondAmazon
2B200200SanaAmazon
3C500600SanaAmazon
3D150600SanaAmazon
4E2020TorcAmazon

 

 How do I calculate the measure for Estimated Total to be able to compare the estimated total with the real value of the invoice?

 InvoiceActual TotalEstimated totalCount ProductCustomer
 12035003Cond
 22002001Sana
 36506002Sana
 420201Torc
Total 1073 13207 


Thank you,
Zosy

2 ACCEPTED SOLUTIONS
barritown
Super User
Super User

Hi @Zosy,

Not sure the solution below is optimal, but it should work:

TotalEstimated = SUMX ( SUMMARIZE ( data, [Invoice], "Result", AVERAGE ( data[Estimated Invoice total] ) ), [Result] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

View solution in original post

Hi @Zosy,

Try this one out:

Estimated Revenue (in selected currency) v2 = 
VAR _tbl = 
ADDCOLUMNS (
    SUMMARIZE (
        'Invoice Details', [Invoice], 
        "Date", 
        MIN ( 'Invoice Details'[Invoice Date] ), 
        "TotEst",
        [TotalEstimated] ), 
        "Rate",
        LOOKUPVALUE(
            'Currency Rates'[exchangerate],
            'Currency Rates'[currency_code], 'Currency Filter'[Selected Currency],
            'Currency Rates'[Year], YEAR ( [Date] ) ) )
RETURN SUMX ( _tbl, [TotEst] * [Rate] )

 

Here's your PBIX with this measure - https://www.dropbox.com/s/lhwz19j80g9glpw/SUM%20of%20invoice%20lines%20with%20dynamic%20exchange%20c...

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

 

View solution in original post

6 REPLIES 6
barritown
Super User
Super User

Hi @Zosy,

Not sure the solution below is optimal, but it should work:

TotalEstimated = SUMX ( SUMMARIZE ( data, [Invoice], "Result", AVERAGE ( data[Estimated Invoice total] ) ), [Result] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Hi @barritown ,

The formula works very well, but I am stuck in the next step. How would I incorporate the TotalEstimated formula in the below measure, where it gets the currency converted dynamically? If I use it like below, it disregards the distinct invoice number.

 

Do you know what I am doing wrong?

 

Estimated Revenue (in selected currency) = 
SUMX( 
'Invoice Details', 
 VAR vExchange =
           LOOKUPVALUE(
                'CurrencyRates'[Exchange Rates],
                'CurrencyRates'[currencycode], 'Currency Filters'[Selected Currency],
                'CurrencyRates'[Year], 'Invoice Details'[Created On].[Year]
                ) 
RETURN 
'Invoice Details'[TotalEstimated]*vExchange
 )

 

Thank you,
Zosy

Hi @Zosy,

I doubt I can help having just this code... If you can break down your new problem the way you did the initial one (with the input data and the desired outcome), there, I'll have more chances. 🙂

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Hi @barritown ,


I have uploaded the file in dropbox. Hope this helps
SUM of invoice lines with dynamic exchange currency PBIX 


What I want to achieve is to calculate the Estimated Revenue (in selected currency) for each individual invoice.

Invoice 1 has  TotalEstimated = 500 but when I convert it to USD (FX rate=2 ) it should be 1000 not 3000 like below.

The result is 3000 because there are 3 prooducts on invoice 1 and it adds the Estimated Invoice Total for all the lines. 500*3=1500 converted to USD equals 3000.

image.png
Invoice details table

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYkMDAyBpCiad8/NSQMK5iVX5eUCGgZG+gam+kYGRkVKsDkSLExAbkabFD4iNidVghGIHhAxOzEtE1mJoqm9gAtJiCNYCMtsZbr4Zdi2W+gZmCFtAWlzAJhGtxQQo5Ap2EowIyS9KRtFgAtUAdFYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Invoice = _t, Product = _t, Price = _t, #"Estimated Invoice total" = _t, Customer = _t, Details = _t, #"Invoice Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice", Int64.Type}, {"Product", type text}, {"Price", Int64.Type}, {"Estimated Invoice total", Int64.Type}, {"Customer", type text}, {"Details", type text}, {"Invoice Date", type date}})
in
    #"Changed Type"

 


Measures Invoice Details table

 

Actual Price (Base currency) = SUM('Invoice Details'[Price])
Actual Price (in selected currency) = 
SUMX( 
'Invoice Details', 
 VAR vExchange =
           LOOKUPVALUE(
                'Currency Rates'[exchangerate],
                'Currency Rates'[currency_code], 'Currency Filter'[Selected Currency],
                'Currency Rates'[Year], 'Invoice Details'[Invoice Date].[Year]
                ) 
RETURN 
'Invoice Details'[Actual Price (Base currency)]*vExchange
 )

 

 

 

TotalEstimated = SUMX ( SUMMARIZE ('Invoice Details', [Invoice], "Result", AVERAGE ( 'Invoice Details'[Estimated Invoice total] ) ), [Result] )

 

 

Estimated Revenue (in selected currency) = 
SUMX( 
'Invoice Details', 
 VAR vExchange =
           LOOKUPVALUE(
                'Currency Rates'[exchangerate],
                'Currency Rates'[currency_code], 'Currency Filter'[Selected Currency],
                'Currency Rates'[Year], 'Invoice Details'[Invoice Date].[Year]
                ) 
RETURN 
'Invoice Details'[TotalEstimated]*vExchange
 )

Currency Rates Table

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUTIyMLQEUq6lRfkgKjRIKVYHRSY0WMElPycnsQjMdgFLm4CljQwwNSLJYNNoDJE2xNSIJINNoxFE2ghTI5IMhsZYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [exchangerate = _t, year = _t, currency_name = _t, currency_code = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"exchangerate", type number}, {"year", Int64.Type}, {"currency_name", type text}, {"currency_code", type text}})
in
    #"Changed Type"

 

 

Currency Filter Table

 

let
    Source = #"Currency Rates",
    #"Removed Other Columns" = Table.SelectColumns(Source,{"currency_code"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"

 

Measures Currency Filter Table

 

Selected Currency = SELECTEDVALUE('Currency Filter'[currency_code])

 


Let me know if you have any other questions

Hi @Zosy,

Try this one out:

Estimated Revenue (in selected currency) v2 = 
VAR _tbl = 
ADDCOLUMNS (
    SUMMARIZE (
        'Invoice Details', [Invoice], 
        "Date", 
        MIN ( 'Invoice Details'[Invoice Date] ), 
        "TotEst",
        [TotalEstimated] ), 
        "Rate",
        LOOKUPVALUE(
            'Currency Rates'[exchangerate],
            'Currency Rates'[currency_code], 'Currency Filter'[Selected Currency],
            'Currency Rates'[Year], YEAR ( [Date] ) ) )
RETURN SUMX ( _tbl, [TotEst] * [Rate] )

 

Here's your PBIX with this measure - https://www.dropbox.com/s/lhwz19j80g9glpw/SUM%20of%20invoice%20lines%20with%20dynamic%20exchange%20c...

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

 

Thank you so much for your help! I couldn't figure it out by myself, having a temporary table with the calculations does an amazing job!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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