Sum column based on 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 Fund" , NAMEOF("measuretable"[sum_of_mutual_fund],0),

("GIC" , NAMEOF("measuretable"[sum_of_gic],1),

("Cash" , NAMEOF("measuretable"[sum_of_cash],2))

Thus, now I have two chart:

Sum of Total customer = 25

By selecting the parameter measure, i can get Mutual fund = 20, GIC = 20, Cash = 20.

However, I am trying to connect the dots between them.

I want to create a measure lets say if i select Mutual fund on the paramenter, it will have Mutual fund = 20 and total customer with Mutual fund = 1+3+3+5 = 12.

May I know if anyone can help?

Thank you!

[ExcelMonke · Accepted solution]

Hello,
Taking the data you have presented at face value, you can consider the following measure:

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     
    )
)

One note: in order to get the table to work and get the result as intended, I did have to create an additional index column so that each customer count can be attributed to a single "index". I had initial problems where it would combine similar customer counts as a single entity, if that makes sense? 

My parameter looks like this:

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

[ljx0648]

A follow up question here my friend,

This Measure works perfectly if I pick one Parameter. 

However, May I know if you can 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).

Please let me know if you have any idea.

Thank you!

[ljx0648]

Works like a charm! Much appreciated