Filter by sum of group by

[afk]

Hi,

I have a dataset that shows the number of visits a URL receives, split into various traffic sources. Is it possible to filter the data in Power BI so that it only shows the data for when a URL has a total number of visits of 2000 or more?

If I were to filter simply by number of visits >= 2000, it will omit the rows for traffic sources that generate less than 2000 visits but the total number for that URL is more than 2000.

Please advise.

Example,

URL	Traffic Source	Visits
URL1	Traffic Source1	2500
URL1	Traffic Source3	600
URL1	Traffic Source3	1300
URL2	Traffic Source1	1200
URL2	Traffic Source3	300
URL3	Traffic Source1	3600
URL3	Traffic Source3	2800
URL3	Traffic Source3	700

Total
URL1	4400
URL2	1500
URL3	7100

Data for URL2 will be omitted because the total number of vists is less than 2000.

Thanks,

afk

[afk]

Hi @Anonymous,

Actually, what I did is create a column to act as a flag using your formula to determine the flag value.

Flag column = IF([Sub_cnt] >= 2000, 1, 0)

Then I did a page filter using this flag column to only show data where the flag is equal to 1, as this is only for URLs with a total number of visits of 2000 or higher, and seemed to do the trick!

Thanks for your help

[Anonymous · Accepted solution]

Hi

Create a measure using this formula:

Sub_cnt = CALCULATE(SUM(Table1[Visits]),ALLEXCEPT(Table1,Table1[URL]))

And then apply a visual filter on this measure, where its >2000.

Thanks

Raj

[afk]

Hi @Anonymous,

I also received the following error message when I tried to copy the DAX formula

"The end of the input was reached"

[Anonymous]

Yes, you can use this in page filter as well.

Please share the snapshot of formula & error your are getting.

Thanks

Raj

[afk]

HI @Anonymous,

I'm no longer getting the error message, but I can't drag the measure into the page level or report level filters.

[afk]

Hi @Anonymous,

Thanks for your suggestion. Is it possible to apply a page or report filter using this?

If not, is there a way around it?

Thanks,

Aqeel