How to create a static column in a table in power bi

[siddhantk989]

Hi,

  I want to create a static column in a table in Power BI, i.e., the values of a particular column should not change if I change my slicer values. Below is the detailed explaination of scenarion:

I have the data as follows:

Data
Category	Parent	Sales
a	abc	100
a	def	200
b	abc	300
b	def	400

Now I am creating 3 measures for this data set. The first 2 are same to calculate sum and the 3rd is to find the difference between the 2.

3 measures

sales = SUM(Sales)

Total sales = SUM(Sales)

Relative Sales = total Sales - Sales

Then I am using Power bI table to show the following data:

Power BI Table
Category	Sales	Total Sales	Relative Sales
a	300	300	0
b	700	700	0

and for slicer I am using Parent value as slicer

Filter

Parent

abc

def

Now if I am not selecting anything in this slicer I am getting my relative sales as 0 which is fine but if I select my Parent as abc for the filter

Filter

Parent

abc

def

I am expecting the below result:

Power BI Table
Category	Sales	Total Sales	Relative Sales
a	100	300	200
b	300	700	400

In the above table you can see that the values of sales changes according to the filter but value of Total Sales remained static. So how do I get this functionality working in power BI?

[Vvelarde · Accepted solution]

@siddhantk989

hi, replace TotalSales measure with:

Total sales = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Category]))

[siddhantk989]

Thanks @Vvelarde The solution worked.