Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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?
Solved! Go to Solution.
hi, replace TotalSales measure with:
Total sales = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Category]))
hi, replace TotalSales measure with:
Total sales = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Category]))
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
75 | |
69 | |
55 | |
38 | |
35 |
User | Count |
---|---|
87 | |
69 | |
59 | |
46 | |
46 |