Forum Discussion
Create a new table from existing one using slicer values
Hello everyone,
I have a Table t with 3 columns - Name of the company, Financial item and Value of the financial item. Financial item represents the name of the certain item in financial report, like cost of employees, revenue from online sales etc.. I am trying to make a dashboard where user can select multiple finacials items and for each item select different range of values. For example, user wants to see all companies that have revenue from online sales bigger than 100k and at the same time tax liabilities lower than 10k.
This is what I have so far:
I created 4 additional tables (Item1,Value1,Item2,Value2). Item1 and Item 2 are identical and they have only one column - Financial_Item which contains distinct Financial Items. Value1 and Value2 are also identical and they contain all values of the Financial items from the original table (Column name = Value). Then, I made 4 slicers:
1. Two dropdown slicers from which you can choose Financial item. One slicer refers to Item1 and other to Item2.
2. Two Range slicer where you select range of values for that Financial item. One slicer refers to Value1 and other to Value2.
Then, I created following measures:
1. Select_Item1 = SELECTEDVALUE(Item1[Financial_Item])
2. Select_Min1 = CALCULATE(MIN(Value1[Value]); allselected(Value1))
3. Select_Max1 = CALCULATE(MAX(Value1[Value]); allselected(Value1))
4. Select_Item2 = SELECTEDVALUE(Item2[Financial_Item])
5. Select_Min2 = CALCULATE(MIN(Value2[Value]); allselected(Value2))
6. Select_Max2 = CALCULATE(MAX(Value2[Value]); allselected(Value2))
So far, every step works perfectly, but I encounter a problem in the next step in which I try to make a new table that contains list of the companies with given conditions using calculated measures. I tried using FILTER, CALCULATETABLE, but nothing has worked so far. I think that the problem is that the measures I have created are not global variables so I can not use them in creating new tables.
Does someone know how can I create global variables from the selected slicer values so I can create new table or maybe can suggest some other solution?
Thank you!
4 Replies
- parry2k
Super User
Anonymous it will be easier to understand the problem if you provide sample data in table format and the expected output.
Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490- AnonymousNot applicable
Hi parry2k ,
Here is sample table:
Name of the company Financial Item Value A Revenue from online sales 110.000,00 A Other revenue 10.000,00 A Cost of revenue 30.000,00 A Cost of employees 20.000,00 A Income before tax 70.000,00 A Interest income 5.000,00 A Tax 8.400,00 A Net income 61.600,00 B Revenue from online sales 105.000,00 B Other revenue 40.000,00 B Cost of revenue 20.000,00 B Cost of employees 10.000,00 B Income before tax 115.000,00 B Interest income 1.000,00 B Tax 13.800,00 B Net income 101.200,00 C Revenue from online sales 150.000,00 C Other revenue 5.000,00 C Cost of revenue 100.000,00 C Cost of employees 50.000,00 C Income before tax 5.000,00 C Interest income 1.000,00 C Tax 600,00 C Net income 4.400,00 D Revenue from online sales 10.000,00 D Other revenue 1.000,00 D Cost of revenue 6.000,00 D Cost of employees 6.000,00 D Income before tax -1.000,00 D Interest income 1.000,00 D Tax 0,00 D Net income -1.000,00 The user will choose financial Item and value range for that financial item. The output shold be the list of companies that meet those conditions. For example, user wants to see which companies have Revenue from online sales bigger than 100.000,00 and at the same time Tax lower than 10.000,00. In our example, only A and C companies meet those conditions so the output should look like this:
Name of the company A C - ante87New Member
Has someone maybe figured out how to do this? I am also interested in solution.