How to Filter Out Zero Rows at Macro Level. Please Help.

[Anonymous]

Hello everyone,

I am having issues figuring out how to correctly filter out zero rows at the macro level. Basically at the macro level, in my case using Foreign field as the macro level, filtering out zero rows cause PBI to show the same line items whether I checked off "Line Items With Activities" or "Line Items Without Activities". It seems PBI is doing this because am not aggregating correctly in my formula; basically, at the entity level, there are entities where certain rows are true zero rows then there are entities with rows that offset each other, i.e. same row item in one entity is positive while the other entity has the exact negative opposite. So in the overview macro level, the items would net out, which is what I want but then when filtering to show either Line Items With or Without Activities, PBI show both when I want the netted row to be also filtered out if it nets to zero. At the entity level, PBI filters out correctly.

How can I write a formula that would accomplish filtering out zero rows, including those netted to zeroes, correctly at the macro level?

As a tack on, how can I filter multiple columns effectively, i.e. I want to filter by entity ID and line item ID so that all line items lined up correctly for the respective entity?

Thank you in advance for the help.

[Anonymous]

Just bumping this up to see if anyone has an answer. Thanks.

[v-chuncz-msft]

@Anonymous ,

You may just try Visual level filters.

[Anonymous]

I would opt for visual level filters but we want to make it dynamic as it'll be handed over to a client and thus having the slicer in the visual to allow toggling to remove rows with zero value would be best. Is this not doable?

[Anonymous]

Here is a screenshot of the mocked up dataset

Unfortunately am on my work laptop and permission is very limited for file sharing.

Thanks again for any help.

[Ashish_Mathur]

Hi,

Without a dummy dataset, it will be very difficult to help you.

[Anonymous]

Hello there,

The mocked up dataset is posted as a screenshot in second post. Unfortunately, this account is my work account and admin has set quite a limitation on uploading and sharing files.