Forum Discussion

ljx0648's avatar
ljx0648
Helper III
2 years ago
Solved

Sum column based on Parameter

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!

Need Help
Show and Tell
Tips and Tricks
  • ExcelMonke's avatar
    ExcelMonke
    2 years ago

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

     

     

     

3 Replies

  • ExcelMonke's avatar
    ExcelMonke
    Impactful Individual
    2 years ago

    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's avatar
      ljx0648
      Helper III
      2 years ago

      Works like a charm! Much appreciated 

    • ljx0648's avatar
      ljx0648
      Helper III
      2 years ago

      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!