Forum Discussion
Subtotal based on condition from other rows
Hello everyone,
I have the following example table, where Brands 1 and 2 are from my company:
| Store | Brand | Value |
| S1 | B1 | 500 |
| S1 | Other | 285 |
| S2 | B2 | 108 |
| S2 | Other | 508 |
| S3 | Other | 251 |
| S4 | B1 | 208 |
| S4 | B2 | 999 |
What I would like to calculate is the total value of the stores that my brands are present, that is, the total value of the store (my brands + others).
For exemple, considering Brand1 I should see: (500 + 285 + 208 + 999) = 1992.
considering Brand 2 I should see: (108 + 508 + 208 + 999) = 1823.
My output should be a table that look like this:
| B1 | 1992 |
| B2 | 1823 |
| Total | 2608 |
Anonymous Maybe:
Measure = VAR __Brand = MAX('Table'[Brand]) VAR __Stores = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table'),[Brand]=__Brand),"Store",[Store])) RETURN SUMX(FILTER(ALL('Table'),[Store] IN __Stores),[Value])
2 Replies
- Greg_Deckler
Community Champion
Anonymous Maybe:
Measure = VAR __Brand = MAX('Table'[Brand]) VAR __Stores = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table'),[Brand]=__Brand),"Store",[Store])) RETURN SUMX(FILTER(ALL('Table'),[Store] IN __Stores),[Value])