Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ljx0648
Helper III
Helper III

Sum column based on Multiple Parameter

Hi guys,

I have a datatabel like this

ljx0648_0-1715275789260.png

 

Custmer count = the total number of customer with the assets on the right.

 

I have also created a field paramter 

 

Parameter = {
    ("Mutual Funds", NAMEOF('DataTable'[Mutual Funds]), 0),
    ("GIC", NAMEOF('DataTable'[GIC]), 1),
    ("CASH", NAMEOF('DataTable'[CASH]), 2)
}

 

I am able to connect both with the paremeter (solution provided by @ExcelMonke )

CustomerCount = 
VAR _MF = CALCULATE(SUM('DataTable'[Customer Count]),FILTER('DataTable',NOT ISBLANK('DataTable'[Mutual Funds])))
VAR _GIC = CALCULATE(SUM('DataTable'[Customer Count]),FILTER('DataTable',NOT ISBLANK('DataTable'[GIC])))
VAR _SGD = CALCULATE(SUM('DataTable'[Customer Count]),FILTER('DataTable',NOT ISBLANK('DataTable'[CASH])))

RETURN
if(
    HASONEVALUE(Parameter[Parameter Order]),
    SWITCH(
        VALUES(Parameter[Parameter Order]),
        0, _MF,
        1, _GIC,
        2, _SGD     
    )
)

 

Thus now,  i select Mutual fund on the paramenter, it will have Mutual fund = 20 and total customer with Mutual fund = 1+3+3+5 = 12.

 

However, May I know if someone cann show me a trick how does it work with two or even three parameters?

 

For example, 

Mutual fund = 12 customer

GIC = 11 customer

Cash = 12 Customer.

 

Now I want to see how many customer I have with both GIC and Cash, so I multi-select both.

 

The total is 40 and customer count is 3+3 = 6 (because only these 2 groups of customer have GIC and Cash at the same time).

 

Any tips & tricks are appreciated!

 

Thank you

2 REPLIES 2
v-nuoc-msft
Community Support
Community Support

Hi @ljx0648 

 

I did a lot of testing for the question you asked.

 

It turns out that you can't compare their same values by selecting multiple parameters.

 

Because your parameter is a column name, when you select multiple parameters, you cannot merge the results of the two columns into one column.

 

This is the main reason why you will get blanks.

 

Perhaps you can do this by breaking up a table into multiple tables based on columns. It's the most effective way I can think of it.

 

Hope you can find a more suitable way to solve the problem.

 

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for your advise Nono. I will give it a shot

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.