Sum on filtered result

[Anonymous]

So basically I have non distinct entries of people with planed days of work (2nd screenshot). A Person can only work 220 days per year, so I want to show only persons that have planed days > 220. so far so I good. I finally managed to write a measure that only shows me the persons wtih planned days > 220. BUT, the sum of my measure sums up ALL of the persons, not only of those who have more than 220 days.

Sure I can turn of the grand total, but I would like to see the grand total of the persons that have more than 220 planed days.

[v-xjiin-msft]

Hi @Anonymous,

In Power BI desktop, when you put a numeric filed in Values of table visual. It can be aggregated here. It also means there's no need to use a measure to do a simple sum. And also you can use visual level filter to filter the values based on your condition.

Please refer to following sample:

As you can see in my sample. They all using the same dataset. The first table visual is my sample source data. The second table is the result of doing SUM in Values. Then the third table is defining a Visual level filter to filter the sum of Plan-PT Gesamt 2018 is greater than 220.

Thanks,
Xi Jin.

[Anonymous]

Thanks Xi Jin,

the problem ist that I do have only one table and secondly I can't use the visual filter, since I'm doing this in Excel DAX at the moment. We will switch to PowerBI sooner or later. But right now, I need a DAX formula which sums the grand total of only persons who have "Plantage Gesamt" > 220. And there are several ROWS with the same person, so you also have to sum up these first. I can't get it work.. At least the correct grand total..