Forum Discussion

stefani_vileva's avatar
stefani_vileva
Resolver II
3 years ago
Solved

Calculating sum based on filtered category

Hello everyone,

 

I want to calculate the sum of quantites of some products, but I have several problems. I am showing only the filtered data (based on all other categories except premium), and I want to calculate the sum of all the products where they are premium, but only for articles that are shown in the table. All of this, needs to be also calculated based on the data for the previous week (which I keep in other data table).

For example,

Article IDCategoryTotal by categoryOverall total
123x3100
234x120
123y32100
123y1100


I have used the following formula, and it calculates right, but the problem is if there is no total by category it returns a value, which is not what i want, and plus if there is no category it adds automatically some categories in the table.

 

VAR QUANTITY_PREVIOUS_WEEK = 
CALCULATE(
    SUM(Orders[packs]), 
    FILTER(ALL('Date'), 'Date'[Week rank]=PREVIOUS_WEEK),
    FILTER(ALL(Orders[Category]), Orders[Category]="Premium")
)

 

Thank you in advance for your help.

 

Kind regards,

Stefani

  • I have solved my problem. It looked like I should show only the only that have no blank rows with total by category.

1 Reply

  • I have solved my problem. It looked like I should show only the only that have no blank rows with total by category.