topic Re: How to calculate column based on Slicer in DAX Commands and Tips

How to calculate column based on Slicer

Anonymous — Thu, 11 Mar 2021 04:43:03 GMT

Hi everyone,

I have a table like the next one:

ID	Class	Points	Points_Norm
1	A	50	0
2	A	800	0.1372455
3	A	911	0.15755784
4	B	456152	83.4639301
5	B	1521	0.26918418
6	B	123	0.01335856
7	C	14894	2.71636296
8	C	4561	0.82548594
9	C	546516	100
10	C	146516	26.8023994

Also, I have a slicer for the column "Class". My objective is to put the variable "Points_norm "in a graph (it is just the values of "Points" normalized between 0 and 100).

What I'm looking for is that, when I select one of the classes with my slicer, I get the normalized values just for the filtered results. In this example, if I select Class==A with the slicer, what I would get is:

ID	Class	Points	Points_Norm
1	A	50	0
2	A	800	87.1080139
3	A	911	100

Because the normalization is done just for those values I calculated. Does anyone know how to get this in Power BI?

Thank you all

Re: How to calculate column based on Slicer

amitchandak — Thu, 11 Mar 2021 05:24:18 GMT

Anonymous , You can not calculate a column with slicer values. You can only use the slicer value in measure. Think how can you use measure in place of column

Re: How to calculate column based on Slicer

Anonymous — Thu, 11 Mar 2021 05:36:03 GMT

So the idea would be, how to get a measure that scales between 0 and 100 the values that affected the slicer?

Re: How to calculate column based on Slicer

amitchandak — Thu, 11 Mar 2021 08:05:33 GMT

Anonymous , have slicer using what if parameter

assume you need it for points and ID is you min level

Try like

total point = sum(Table[Points])

measure =
var _min = minx(allselected(whatif), whatif[value])
var _max = maxx(allselected(whatif), whatif[value])
return
sumx(values(Table[ID]) , if([total point]>=_min && [total point] <=_max, [total point], blank()))

or like this, here you can add more than one group by

measure =
var _min = minx(allselected(whatif), whatif[value])
var _max = maxx(allselected(whatif), whatif[value])
return
sumx(filter(summarize(Table, Table[ID], "_1", sum(Table[Points])) , [_1]>=_min && [_1] <=_max),[_1])