CODEWITHSUNDEEP
powerbi
Juan Xu
Juan Xu

Reputation: 1

How to pick up column by given parameters and then sum-up the column total in Power BI

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

Upvotes: 0

Views: 254

Answers (1)

Andrey Nikolov
Andrey Nikolov

Reputation: 13460

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:

enter image description here

Upvotes: 1

Related Questions