Forum Discussion
Helper IIIFilter data in heirachy
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.
Any help would be great.
Still doesnt quite sort it out. Its ok I have managed to find a way to do it.
5 Replies
- amitchandak
Super User
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]))
av9Its 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] ) )
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
