Filter data in heirachy

[av9]

I am trying to filter data in this table so any customer above 15% should show only.

I am having troubles creating a filter that allows me to filter at this level and keep the account rows under them when the account rows are less than 15% but add to greather than 15% on a customer level.

As example Customer 1 is totalled 16% but because the accounts are both only 8% these rows are getting filtered out.

% Sales = DIVIDE([Total Sales], CALCULATE([Total Sales], All(Customer), All (Account)))

Any help would be great.

[Anonymous]

HI, 

you can use ISINSCOPE() function to work on the hierarchy level that you're interested in.

Here's the guide: Distinguishing HASONEVALUE from ISINSCOPE - SQLBI

[amitchandak]

@av9 , create a measure like this and use visual level filter

% filter Sales = calculate(DIVIDE([Total Sales], CALCULATE([Total Sales], all())), allexcept(table, Table[customer]))

[av9]

Its creating the visual level filter thats the issue. Seems to always not include the 3rd level in heirarchy (Account)

[danextian]

You can try something like below which will calculate the percentage on the account level.

Account % =
CALCULATE ( [% Measure], ALLEXCEPT ( tbl, tbl[account] ) )

[av9 · Accepted solution]

Still doesnt quite sort it out. Its ok I have managed to find a way to do it.