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" , ...
  • ExcelMonke's avatar
    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)
    }