Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
93 | |
89 | |
35 | |
35 |
User | Count |
---|---|
154 | |
102 | |
82 | |
64 | |
54 |