Power BI 中的货币换算

2020-09-25 16:45:25 ⋅ 2036 ⋅ 1 ⋅ 0

本文翻译自国际Power BI大师Marco Russo的文章——《Currency conversion in Power BI reports》，本文介绍如何在Power BI中实现用于报告目的的货币换算。**

应用于报表的货币换算可以包括许多不同的方案：
多币种数据，使用单一货币报告；
多币种数据，使用多币种报告；
单一货币数据，使用多币种报告。

经验是预先应用货币汇率进行转换。因此，在数据导入模型时将所有金额转换为报表中的单一币种对于解决此类场景是个不错的办法。当您需要创建多币种的报表时，预先计算数据可能具有挑战性且成本高昂。因此，基于DAX度量和经过适当设计的数据模型的动态解决方案意义匪浅。

在本文中，我们仅考虑上述的第三种情况，即“使用单一货币的数据，使用多币种进行报告”。第二种情况可以通过转换数据来实现，以便以一种货币将其导入模型中，从而将视线转移到我们今天主要讨论的情况。

引入数据模型

我们考虑的数据模型包括一个销售表，其中所有交易都以美元作为货币记录。还有一个隐藏的汇率表，其中包含每个月中每种货币的汇率，分配给每个月的第一天。

average agerate列是我们认为当月的平均汇率，它必须作为转换率应用于当月包含的所有交易。下图显示了与此描述相关的表。

file

完整的模型还包括其他表，如产品表和顾客表。下面的报告显示了2008年手机销量表的“Sales Amount”度量。所有的金额都是以美元计算的，美元是用来在销售表中记录交易的单一货币。

file

目标是使用户能够通过使用切片器选择不同的币种，从而获得如下所选货币为欧元的报告。

file

在DAX中实现货币换算

Sales Currency度量值将所需的货币换算应用于Sales Amount度量值的结果。为了在DAX中取得良好的性能，最好是将以同一货币和汇率计算的所有金额汇总起来，然后将换算率应用于该总额，而不是将换算应用于每一笔交易。由于此场景中的汇率为每月粒度，因此Sales Currency度量是通过按月对Sales Amount进行分组来实现的，如以下代码所示：


Sales Currency :=
IF (
    ISCROSSFILTERED ( 'Currency' ),
    VAR SelectedCurrency =
        SELECTEDVALUE ( 'Currency'[Currency Code] )
    VAR DatesExchange =
        SUMMARIZE (
            ExchangeRate,
            'Date'[Calendar Year Month Number],
            'ExchangeRate'[AverageRate]
        )
    VAR Result =
        IF (
            NOT ISBLANK ( SelectedCurrency ),
            IF (
                SelectedCurrency = "USD",
                [Sales Amount],
                SUMX (
                    DatesExchange,
                    [Sales Amount] * 'ExchangeRate'[AverageRate]
                )
            )
        )
    RETURN
        Result,
    [Sales Amount]
)

DatesExchange变量在内存中创建一个表，该表每个月有一行。假设当月有一个单一的汇率，并且选择了一种单一的货币。如果不是这样，这个度量将返回不准确的数字。

Result变量将汇率应用于当月销售额的结果，计算每个月的换算。如果有多种货币可供选择，结果为空白，而如果SelectedCurrency为美元，则返回销售额的值，而不执行任何转换。

您可能需要修改销售币种的业务逻辑，以使公式更好地适应您的特定要求。例如，如果用户选择两种或两种以上货币，则当前显示返回空白，但您可能希望在这种情况下提示错误或显示不同的结果。

利用计算组

使用特定度量(如Sales Currency)定义货币换算有两个缺点：

1.代码和度量重复：货币换算需要应用于显示货币金额的所有度量。如果您有3个度量值(销售额、总成本和利润)，则必须创建另外3个度量值，以显示具有所需报告货币的值。

2.格式字符串：Sales Currency度量结果的格式字符串不包含货币符号。在处理具有选定货币的报表时，仅通过查看数字可能很难理解报表使用哪种货币。
file

通过在计算组中实现货币换算，可以解决这两个问题。在编写本文时，Power BI中的计算组不可用。因此，目前本文的其余部分只能在Azure Analysis Services或Analysis Services 2019中实现。

将货币换算应用于任何度量值的计算项的表达式类似于Sales Currency度量值的定义。它只是将Sales Amount度量引用替换为对SELECTEDMEASURE函数的调用：

--
-- Calculation Group: Currency Conversion
-- Calculation Item : Report Currency
-- 
VAR SelectedCurrency =
    SELECTEDVALUE ( 'Currency'[Currency Code], "USD" )
VAR DatesExchange =
    ADDCOLUMNS (
        SUMMARIZE (
            ExchangeRate,
            'Date'[Calendar Year Month Number]
        ),
        "@ExchangeAverageRate", CALCULATE (
            SELECTEDVALUE ( 'ExchangeRate'[AverageRate] )
        )
    )
VAR Result =
    IF (
        ISBLANK ( SelectedCurrency ) || SelectedCurrency = "USD",
        SELECTEDMEASURE (),
        -- Single Currency non-US selected
        SUMX (
            DatesExchange,
            SELECTEDMEASURE () * [@ExchangeAverageRate]
        )
    )
RETURN
    Result

由于此转换可以应用于任何度量值，包括百分比和非货币度量值(如#Quantity)，因此检查是否应该转换度量值是一种很好的做法。为了在将新度量添加到数据模型时最大限度地减少将来的维护需求，我们可以为度量名称定义命名约定：

-如果度量名称包括“#”，则度量表示数量，而不是货币值。不应用货币换算。

%-如果度量名称包括“%”，则度量表示百分比，而不是货币值。不应用货币换算。

这样计算项定义更长，但更灵活：

-- 
-- Calculation Group: Currency Conversion
-- Calculation Item : Report Currency
-- 
VAR MeasureName =
    SELECTEDMEASURENAME ()
VAR SkipConversion =
    NOT ISCROSSFILTERED ( 'Currency' )
        || ( SEARCH ( "#", MeasureName, 1, 0 ) > 0 )
        || ( SEARCH ( "%", MeasureName, 1, 0 ) > 0 )
RETURN
    IF (
        SkipConversion,
        [Sales Amount],
        VAR SelectedCurrency =
            SELECTEDVALUE ( 'Currency'[Currency Code] )
        VAR DatesExchange =
            SUMMARIZE (
                ExchangeRate,
                'Date'[Calendar Year Month Number],
                'ExchangeRate'[AverageRate]
            )
        VAR Result =
            IF (
                NOT ISBLANK ( SelectedCurrency ),
                IF (
                    SelectedCurrency = "USD",
                    [Sales Amount],
                    SUMX (
                        DatesExchange,
                        [Sales Amount] * 'ExchangeRate'[AverageRate]
                    )
                )
            )
        RETURN
            Result
    )

通过使用计算组，还可以通过计算项的格式字符串表达式属性对格式字符串进行动态控制。例如，如果对Currency表进行了选择，则以下表达式使用Currency[Currency Format]列作为格式字符串：

-- 
-- Calculation Group: Currency Conversion
-- Calculation Item : Report Currency
-- Format String Expression
-- 
VAR MeasureName = SELECTEDMEASURENAME()
VAR SkipConversion =
    NOT ISCROSSFILTERED ( 'Currency' )
        || (SEARCH ( "#", MeasureName, 1, 0 ) > 0)
        || (SEARCH ( "%", MeasureName, 1, 0 ) > 0)
VAR CurrencyFormat =
    SELECTEDVALUE ( 'Currency'[Currency Format], "#,0.00" )
VAR Result =
    IF (
        SkipConversion,
        SELECTEDMEASUREFORMATSTRING(),
        CurrencyFormat 
    )
RETURN
    Result

使用前面的格式字符串表达式，我们得到以下结果。

file