如何通过给定参数选择列,然后在Power BI中汇总列总数 [英] How to pick up column by given parameters and then sum-up the column total in Power BI
问题描述
我有一个如下表。我想先通过给定的参数提取列,然后对列的总数求和。
例如,通过给定的 SGD 参数,我想总结一下列SGD的总数。
日期。澳元新元港币
2019年7月1日SO1 100 105.17 545.74
2019年5月5日SO2 130 122.01 691.13
2019年9月9日SO3 160 150.32 853.55
9/15 / 2019 SO4 180 169.11 960.25
谢谢
一种实现此目的的方法是添加一个新的断开连接的表 Currency
,其中一列 Currency
和值 AUD
, SGD
和 HKD
。
然后创建一个度量,该度量将获取切片器的值并在相应列上计算合计,具体取决于滑块中的选择:
总计= SWITCH(SELECTEDVALUE('Currencies'[Currency]; AUD);
AUD; SUMX('Table'; [AUD]);
SGD; SUMX('Table'; [SGD]);
HKD; SUMX('Table'; [ HKD]);
SUMX('Table'; [AUD]))
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屋!