Forum Discussion
Sum column based on Parameter
- 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) }
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)
}
Works like a charm! Much appreciated