Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
av9
Helper III
Helper III

Filter 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.

 

av9_0-1650638690710.png

 

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

 

Any help would be great.

1 ACCEPTED SOLUTION

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

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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
Super User
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]))

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

 

av9_0-1650935564450.png

 

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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.