How to use the same column to calculate two dimension

[KH_Mike]

Hi All,

I have a data like below and want to have two separate measure to calculate and put it into a scatter chart X & Y axis. I tried to create new column by using existing "Type" column. and Make the "Type" and "New Type" as a slicer. Then set both slicer not filter together. However, when I try to create two measure like below but seems it will affected by the "Type" and "New Type" selection.

X Axis = calculate(sum([Amount]),removefilters([New Type]))

Y Axis = calculate(sum([Amount]),removefilters([Type]))

Type	Amount
A	10
A	20
B	30
B	40
C	50
C	60
D	70
D	80
E	90
E	100

[v-xiaotang · Accepted solution]

Hi @KH_Mike 

Thanks for reaching out to us.

>> Then set both slicer not filter together.

First you need to create 2 dimension tables, 'X Axis' & 'Y Axis'.

then please go to Model view, make sure these 3 tables are independent from each other,

then create 2 measures,

X Axis = calculate(sum('Table'[Amount]),FILTER('Table','Table'[Type] in ALLSELECTED('X Axis'[Type])))

Y Axis = calculate(sum('Table'[Amount]),FILTER('Table','Table'[Type] in ALLSELECTED('Y Axis'[Type])))

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@KH_Mike , You need at least one independent table

Type new = distinct(Table[Type])

Then you need have a measure

Calculate(sum([Amount]),removefilters([Type]), keepfilters(Type[Type]))

or

Calculate(sum([Amount]),Filter(all(Table[Type]), Table[Type]) in values(Type[Type])) )

Also refer

Need of an Independent Table in Power BI - Exclude: https://youtu.be/lOEW-YUrAbE

Compare two Brands: https://youtu.be/exN4nTewgbc