Forum Discussion
Alex1871
3 years agoFrequent Visitor
Sum over distinct values Filtered by date
I have a table that follows the idea of something like this:
| Date | Product | Category | Budget |
| June | Prod 1 | Shoe | 30000 |
| June | Prod 2 | Shoe | 30000 |
| June | Prod 3 | Jacket | 20000 |
| June | Prod 4 | Short | 10000 |
| May | Prod 1 | Shoe | 40000 |
| May | Prod 2 | Shoe | 40000 |
| May | Prod 3 | Jacket | 20000 |
| May | Prod 4 | Short | 10000 |
As you can see, every product is assigned to a category, and every categroy has a budget. For example, 40000 is the budget for the entire shoe category. This budget will change monthly. I simply want to calculate the entire budget as a number, to use it for further calculations, and this should be filtered by month.
Now I cant just sum over the budget monthly, because there are multiple shoe products. I tried the following command which returns a table and the correct value, if I chose to represent it as a column chart, the date filter is applied through another slicer field:
total_budget= SUMX(
SUMMARIZE( TableA, TableA[Category], "A", MAX(TableA[Budget])),
[A]
)
I would appreciate any help or ideas, not necessarily building upon my total_budget formula.