Forum Discussion
Slicer by columns name
Hello, I have the following table: Sale_ID Unity_Price Tax_1 Tax_2 Tax_3 Tax_4 1 10 5 15 8 4 2 40 30 25 8 7 To calculate the final price of a sale I created a...
Hi Anonymous,
I would solve this using a measure do the following:
- Create a table (do not related this with any other tables with the following structure:
- Name Slicer - Selection: Unity_Price, Tax_1, Tax_2, Tax_3, Tax_4
- Add the following measure to your data table:
Final_Price = VAR Unity_price = IF ( CONTAINS ( Slicer, Slicer[Selection], "Unity_Price" ) = TRUE (), SUM ( Sales[Unity_Price] ), 0 ) VAR Tax1 = IF ( CONTAINS ( Slicer, Slicer[Selection], "Tax_1" ) = TRUE (), SUM ( Sales[Tax_1] ), 0 ) VAR Tax2 = IF ( CONTAINS ( Slicer, Slicer[Selection], "Tax_2" ) = TRUE (), SUM ( Sales[Tax_2] ), 0 ) VAR Tax3 = IF ( CONTAINS ( Slicer, Slicer[Selection], "Tax_3" ) = TRUE (), SUM ( Sales[Tax_3] ), 0 ) VAR Tax4 = IF ( CONTAINS ( Slicer, Slicer[Selection], "Tax_4" ) = TRUE (), SUM ( Sales[Tax_4] ), 0 ) RETURN Unity_price + Tax1 + Tax2 - Tax3 - Tax4- Now just add the valuies from the table you created to a slicer and your measure to the table should give the expected result:
Regards,
MFelix
- Create a table (do not related this with any other tables with the following structure:
MFelix
Super User
Super UserHi Anonymous,
You can do this making of 3 options
- Dynamic Hierarchie
- Unpivot Columns
- Bookmarks
- Dynamic Hierarchie
Using this post I adapted the situation and made the following
- Created a new table with the following code:
Country Job Hierarchy =
UNION (
SELECTCOLUMNS (
'Hierarchy';
"Badge ID"; 'Hierarchy'[BadgeID];
"Hierarchy Name"; 'Hierarchy'[Job Title];
"Level"; "Job Title";
"LevelNumber"; 1
);
SELECTCOLUMNS (
'Hierarchy';
"Badge ID"; 'Hierarchy'[BadgeID];
"Hierarchy Name"; 'Hierarchy'[Country];
"Level"; "Country";
"LevelNumber"; 2
)
)j
- Make a relationship between this table and your main table by BadgeID: be aware that this will create a many to many relationship. If you don't have the latest version of PBI you need to make a dimension table for Uniques BadgeID and then relate this with the two other table
- Make this relationship with a cross filter in both directions
- Add the Column Hierarchy name to your legend and the count of what ever field you want from the main table.
2. Unpivot Column (link to unpivot columns)
- Query Editor Select both country and Job title column
- Transform Unpivot
- Get 2 columns atribute and value
- Add Attribute to slicer and value to legend in chart
3. Bookmarks (link to bookmarks documentation)
- Create two buttons Job title Country
- Create two donut charts one with job title and the cother with country on legend
- Hide the Job Title chart and create the bookmark named country and make it an action of the Country button and vice-versa
- Then just click on the buttons (on desktop version you have to use CTRL + CLICK)
See below the screenshot and the PBIX file with all the options above.
Regards,
MFelix