Power BI重复发票行仅求和一次并实现动态货币转换

我手上有一组发票产品明细数据，当一张发票包含多个产品时，发票的预估总额会在多行重复显示。我需要计算单张发票的选定货币预估收入——比如发票1的TotalEstimated是500，转换为USD（汇率=2）时结果应该是1000，但现在因为该发票有3个产品行，系统把每行的预估总额累加后再转换，得到了3000（50032）的错误结果。

相关表与现有度量值 #

发票明细表（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"

发票明细表度量值 #

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

汇率表（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"

货币筛选表度量值 #

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

尝试失败的度量值 #

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
 )

解决方案 #

问题核心是原度量值用SUMX遍历明细行时，每行都重复乘以了发票级别的TotalEstimated，导致重复计算。正确逻辑是先按发票分组汇总预估总额，再对每个发票单独应用汇率转换，最后求和。

修改后的度量值如下：

Estimated Revenue (in selected currency) = 
SUMX(
    // 按发票分组，获取单发票的预估总额与对应年份
    SUMMARIZE(
        'Invoice Details',
        'Invoice Details'[Invoice],
        "InvoiceYear", YEAR('Invoice Details'[Invoice Date]),
        "InvoiceEstimated", AVERAGE('Invoice Details'[Estimated Invoice total])
    ),
    // 对每个发票匹配汇率并计算转换后金额
    VAR vExchange = LOOKUPVALUE(
        'Currency Rates'[exchangerate],
        'Currency Rates'[currency_code], 'Currency Filter'[Selected Currency],
        'Currency Rates'[year], [InvoiceYear]
    )
    RETURN [InvoiceEstimated] * vExchange
)

关键说明 #

1. SUMMARIZE按发票分组，确保每个发票只被处理一次，避免明细行重复数据的干扰。
2. 提取发票年份用于精准匹配汇率表中的年度汇率。
3. 对每个发票单独计算转换后金额，最后汇总得到正确的总预估收入。

内容的提问来源于stack exchange，提问作者Zosy