如何通过给定参数提取列，然后在 Power BI 中汇总列总数 [英] How to pick up column by given parameters and then sum-up the column total in Power BI

问题描述

我有一张如下表.我想先通过给定的参数提取列，然后对列的总数求和.

例如，通过SGD的给定参数，我想总结一下列SGD的总量.

日期 SO NO.澳元 新元 港币2019 年 7 月 1 日 SO1 100 105.17 545.742019 年 8 月 5 日 SO2 130 122.01 691.132019 年 9 月 9 日 SO3 160 150.32 853.552019 年 9 月 15 日 SO4 180 169.11 960.25

谢谢

解决方案

实现此目的的一种方法是添加一个新的断开连接的表 Currencies，其中包含一列 Currency 和值AUD、SGD 和 HKD.为其添加切片器并使其下拉.

然后创建一个度量，它将获取切片器的值并在相应列上计算总计，具体取决于滑块中的选择:

总计 = SWITCH(SELECTEDVALUE('Currencies'[Currency]; "AUD");澳元"；SUMX('表'; [澳元]);新元"；SUMX('表'; [SGD]);港币"；SUMX('表'; [港币]);SUMX('表'; [澳元]))

SELECTEDVALUE('Currencies'[Currency]; "AUD") 将返回切片器中选择的值，如果没有选择或选择多个值，则返回 AUD.请参阅

I've got a table like below. I would like to pickup the column first by given parameters then sum up the total of the column.

For example, by given Parameter of SGD, I would like to sum-up the total amount of column SGD.

Date     SO NO. AUD         SGD             HKD
7/1/2019    SO1 100      105.17          545.74 
8/5/2019    SO2 130      122.01          691.13 
9/9/2019    SO3 160      150.32          853.55 
9/15/2019   SO4 180      169.11          960.25 

Thanks

解决方案

One way to achieve this is to add a new disconnected table Currencies with one column Currency and values AUD, SGD and HKD. Add a slicer for it and make it drop down.

Then create a measure, which will take the value of the slicer and calculate total on the corresponding column, depending on the selection in the slider:

Total = SWITCH(SELECTEDVALUE('Currencies'[Currency]; "AUD");
    "AUD"; SUMX('Table'; [AUD]);
    "SGD"; SUMX('Table'; [SGD]);
    "HKD"; SUMX('Table'; [HKD]);
    SUMX('Table'; [AUD]))

SELECTEDVALUE('Currencies'[Currency]; "AUD") will return the value selected in the slicer, or AUD if none or multiple values are selected. See SELECTEDVALUE.

SWITCH will compare this value with a list of possible options (AUD, SGD and HKD) and return corresponding expression (SUMX('Table'; [AUD]), SUMX('Table'; [SGD]) or SUMX('Table'; [HKD])), or some default value if there is no match (SUMX('Table'; [AUD])).

Then use this measure in your report, and it's value will change depending on the selection in the slicer:

这篇关于如何通过给定参数提取列，然后在 Power BI 中汇总列总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！