How to Remove Filters being applied by Field Parameters?

[Anonymous]

I have a dataset that is summing up Busines Costs for the years 2015 - 2020. The dataset is shaped like this:

Region Level 4	Legal Level 9	Product Group Level 5	Year	Cost Type	Cost Value
...	...	...	YYYY	Fixed Cost	$
...	...	...	YYYY	Variable Cost	$
...	...	...	...	...	...

Region Level 4, Ledger Level 9, and Product Group Level 5 represent the most granular levels of Cost Breakdown Hierarchies. Connected to those columns, respectively, are tables with the following headers:

Region Level 1

Region Level 2

Region Level 3

Region Level 4

Legal Level 1

Legal Level 2

Legal Level 3

Legal Level 4

Legal Level 5

Legal Level 6

Legal Level 7

Legal Level 8

Legal Level 9

Product Group Level 1

Product Group Level 2

Product Group Level 3

Product Group Level 4

Product Group Level 5

I am using a Field Parameter called 'Cost Type' to allow users to select between these hierarchies when breaking down cost. I also modified the Field Parameter to have a "Grouping" field, as seen here:

Cost Type = {
("RL1", NAMEOF('Region_Hierarchy'[Reg Level 1]), 0, "Region"),
("RL2", NAMEOF('Region_Hierarchy'[Reg Level 2]), 1, "Region"),
("RL3", NAMEOF('Region_Hierarchy'[Reg Level 3]), 2, "Region"),
("RL4", NAMEOF('Region_Hierarchy'[Reg Level 4]), 3, "Region"),
("LL1", NAMEOF('Legal_Hierarchy'[Legal Hierachy 1]), 4, "Legal"),
("LL2", NAMEOF('Legal_Hierarchy'[Legal Hierachy 2]), 5, "Legal"),
("LL3", NAMEOF('Legal_Hierarchy'[Legal Hierachy 4]), 6, "Legal"),
("LL4", NAMEOF('Legal_Hierarchy'[Legal Hierachy 4]), 7, "Legal"),
("LL5", NAMEOF('Legal_Hierarchy'[Legal Hierachy 5]), 8, "Legal"),
("LL6", NAMEOF('Legal_Hierarchy'[Legal Hierachy 6]), 9, "Legal"),
("LL7", NAMEOF('Legal_Hierarchy'[Legal Hierachy 7]), 10, "Legal"),
("LL8", NAMEOF('Legal_Hierarchy'[Legal Hierachy 8]), 11, "Legal"),
("LL9", NAMEOF('Legal_Hierarchy'[Legal Hierachy 9]), 12, "Legal"),
("PG1", NAMEOF('Product_Group_Hierarchy'[Product Group 1]), 13, "Product Group"),
("PG2", NAMEOF('Product_Group_Hierarchy'[Product Group 2]), 14, "Product Group"),
("PG3", NAMEOF('Product_Group_Hierarchy'[Product Group 3]), 15, "Product Group"),
("PG4", NAMEOF('Product_Group_Hierarchy'[Product Group 4]), 16, "Product Group"),
("PG5", NAMEOF('Product_Group_Hierarchy'[Product Group 5]), 17, "Product Group"),
}

Additionally, I am using the "Show Values of Selected Field" option of the Field Parameter to allow users to select which values in the hierarchies they want to display costs for.

This works well, but I want to have a measure that does REMOVEFILTERS() on the filters that are applied by the "Show Values of Selected Field" slicer of the Field Parameter. I want to do this so I can see what % of Total Costs a certain section of RL1 is, for example. 

Say, for example, RL1 has the Field Values "Americas", "Europe", "Africa", and "Asia". I want a measure where, if I select [Field Parameter Group] "Regional" and then "Europe" in [Field Parameter Values], it will show me (Europe Costs)/(Total Costs). 

I just can't figure out how to get (Total Costs) though. When I try applying REMOVEFILTERS() or ALL() to the [Field Parameter Values], it doesn't seem to affect my var TotalCosts value. 

Does anyone know how to do this? Any insights would be appreciated. Thanks!

[Ashish_Mathur]

Hi,

Share the download link of the PBI file and show the expected result.

[Anonymous]

Hi Ashish, 

I am unfortunately unable to attach a download link for a PBI file due to my workspace limitations. However, I can create a simple example of what I'm trying to do, using the Power BI Contoso Dataset: https://www.microsoft.com/en-us/download/confirmation.aspx?id=46801.

After getting that PBIX, I:

1. Created the Row and Column Parameters:

That's how I created the '0_Row_Parameter'. I use the same fields for '0_Column_Parameter'.

2. Add Groupings to the Field Parameters:

After creating the Field Parameters, I Grouped together the Fields from the "Product", and the ones from the "Geography" table. Grouped them as such:

 0_Row_Parameter = {
("ClassName", NAMEOF('Product'[ClassName]), 0, "Product"),
("BrandName", NAMEOF('Product'[BrandName]), 1, "Product"),
("Manufacturer", NAMEOF('Product'[Manufacturer]), 2, "Product"),
("ProductDescription", NAMEOF('Product'[ProductDescription]), 3, "Product"),
("ContinentName", NAMEOF('Geography'[ContinentName]), 4, "Geography"),
("RegionCountryName", NAMEOF('Geography'[RegionCountryName]), 5, "Geography")
}

I used the same logic to create the grouping for the 0_Column_Parameter Field Parameter.

3. Create a Table to hold the "Value" and "% of Row Total" Matrix Headers. 

I used the "Enter Data" function to create this table. It will serve as the top level of the headers for the Matrix visual I want to display. Under it, will go '0_Column_Parameter'[0_Column_Parameter].

4. Create the Measure to deal with the different headers of the Matrix.

Next, I created this measure to display either the level value of [Total Cost] or the % of Row Total Cost, depending on which value from 0_header_Calc[Calc_type] is selected by the measure:

0. Total Cost Modified = 
SWITCH(
TRUE(),
SELECTEDVALUE('0_Header_Calc'[Calc_Type]) = "Value",
CALCULATE(
SUM('Sales'[TotalCost])
),
SELECTEDVALUE('0_Header_Calc'[Calc_Type]) = "% of Row Total",
"Row Total %?"
)

So, this is what my matrix looks like:

 

The only thing I can't get is, how do I actually calculate the "% of Row Total" value for the bottom matrix? In the top matrix, I simply pulled [Total Cost] onto the Values field twice, and made the second one Calculated as a % of Row Total. 

However, that doesn't work for me, because I want "Value" and "% of Row Total" to be split like in the bottom matrix. That means I need to ignore the context of {"Asia", "Europe", "North America"} for each Row level to get Total cost per row, then divide cost by Total Cost per row to get % of Row Total. However, I'm not sure how to do this while keeping the Column Field Parameters.

[amitchandak]

@Anonymous , You should change measure based on selection

like

divide([Cost], calculate([cost], allselected())

example tp switch measure

Switch TOPN with Field Parameters: https://amitchandak.medium.com/switch-topn-with-field-parameters-299a0ae3725f