Forum Discussion
Basic Filter Grouping Help
Hi All,
I have a basic issue that I can't seem to figure out.
I want to create "groups" of amounts: 0-100k, 100 - 500k etc, for my budgets.
I have done this by creating a conditional column in Power Query with the following:
if [Budgets] >= 0 and [Budgets] <= 100000
then "0 - 100k"
else if [Budgets] > 100000 and [Budgets] <= 500000
then "100k - 500k"
else if [Budgets] > 500000 and [Budgets] <= 1000000
then "500k - 1M"
else if [Budgets] > 1000000
then "1M Over"
else "null"
In Power Query it seems to be working fine:
My problem appears when I'm looking at these in a table, it's not taking into consideration the SUM of the categories, just the individual Budget amount, making it seem like it's incorrect. I have the filter "0-100k" selected but get values that are over 100k. (These individual sub-category budgets are actually under 100k, but when added for all the different locations, it's over 100k, how can I make my filter/column understand that)
Any help would be great! 🙂 TIA
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])))
2 Replies
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])))
Hi Sabo256w ,
we are probably looking at Row Context here and trying to apply it on filter context.
try removing summarization from planned budget of your field from your visualization, if you are looking for applying filters on summarized data you should probably create a measure in dax instead of Powerquery.
visual you posted
