Sum column based on Multiple Parameter

[ljx0648]

Hi guys,

I have a datatabel like this

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

[Anonymous]

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.

[ljx0648]

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