Forum Discussion

Alex1871's avatar
Alex1871
Frequent Visitor
3 years ago

Sum over distinct values Filtered by date

I have a table that follows the idea of something like this:

 

DateProductCategoryBudget
JuneProd 1Shoe30000
JuneProd 2Shoe30000
JuneProd 3Jacket20000
JuneProd 4Short10000
MayProd 1Shoe40000
MayProd 2Shoe40000
MayProd 3Jacket20000
MayProd 4Short10000

 

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.

1 Reply