Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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]))
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
65 | |
64 | |
56 | |
39 | |
27 |
User | Count |
---|---|
85 | |
59 | |
45 | |
43 | |
38 |