Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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]))