Forum Discussion
Basic Filter Grouping Help
your M code just classified categories row by row, this is not what you really wanted.
say, one sub-category's amount is 90k, then cost level will be 0-100k, if there are 100 rows of this sub-category, then the sum amount will be 9000k, but it always be cagegoried to 0-100k.
you can create a dimension table to define each cost level's up and low limited, say
cost level min max
0-100k 0 100
.........
then create measure like:
NewMeasure=sumx(filter(values(table[sub-category]), var _amt=calculate(sum(facttable[amount])) return _amt>=max(dimcostleveltable[min])&&_amt<=max(dimcostleveltable[max])),calculate(sum(facttable[amount])))
your M code just classified categories row by row, this is not what you really wanted.
say, one sub-category's amount is 90k, then cost level will be 0-100k, if there are 100 rows of this sub-category, then the sum amount will be 9000k, but it always be cagegoried to 0-100k.
you can create a dimension table to define each cost level's up and low limited, say
cost level min max
0-100k 0 100
.........
then create measure like:
NewMeasure=sumx(filter(values(table[sub-category]), var _amt=calculate(sum(facttable[amount])) return _amt>=max(dimcostleveltable[min])&&_amt<=max(dimcostleveltable[max])),calculate(sum(facttable[amount])))