Agregate, filter and agregate again

[Ister]

Hello all !

I have table CustBalance as datasource with such columns: Year, Month, Customer, CustGroup, Account, Balance.

And I need to do such report:

1. exclude some rows by filter or slicer on Account field,
2. then agregate with SUM(Balance) to table without Account field,
3. then filter on summarized Balance, exclude all non-positive
4. then agregate with SUM(Balance) to table without Customer field and place it in report.

So, in that way I agregate all accounts for each Customer, then take all positive values, and then agregate Customers to CustGroups.

Also it may be need to exclude non negative values on step 3, or keep all values.

The question is it possible in Power BI and DAX, without data manipulations inside datasource?

[v-qiuyu-msft]

Hi @Ister,

Do you mean whether we can use DAX to achieve the requirement same as those four steps? 

If that is a case, it's possible to use DAX expression for dataset to aggregate values based on filtered values. In your scenario, you can use CALCULATE Function, FILTER Function and ALLEXCEPT Function. Perhaps you need to use another function. It would be better if you could share some sample data and screenshots about expected results.

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu