Forum Discussion
ljx0648
2 years agoHelper III
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" , ...
- 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) }