Forum Discussion
Solved
Category-split value divided by a calculated constant
Hi there! I have a fact table which includes sales values with various different categories which can be split into "Main Sales" and "Other Sales" groupings (i.e. Category 1-3 = "Main", 4-6 = "Ot...
Hi richard_jackson ,
Please check the formula:
Column = var a = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Week],'Table'[Category]),'Table'[Main]=0))+0 var b = CALCULATE(SUM('Table'[Quantity]),FILTER(ALLEXCEPT('Table','Table'[Week]),'Table'[Main]=1)) return a/b
Hi lbendlin - thanks for that!
Data set
| Week | Category | Main | Other | Quantity | Value |
| 1 | A | 1 | 0 | 2 | 1 |
| 1 | B | 0 | 1 | 3 | 2 |
| 1 | B | 0 | 1 | 4 | 3 |
| 1 | C | 0 | 1 | 5 | 4 |
| 2 | A | 1 | 0 | 6 | 5 |
| 2 | B | 0 | 1 | 7 | 6 |
| 2 | C | 0 | 1 | 8 | 7 |
Expected outcome
Parameters
Week=1
OtherCategory=1 (i.e. OtherCategory=1 or "true", MainCategory=0 or "false")
MainQuantity=2 (i.e. the sum of Quantity for any values for that week where MainCategory=1)
| Week | Category | Value | Value/MainQuantity |
| 1 | B | 5 | 2.5 |
| 1 | C | 4 | 2 |
Any guidance would be greatly appreciated!
Thanks!
Rich
lbendlin
Super User
Super Usersomething like this?
You would also have to specify how the totals should be computed.
Hi there - thanks for that!
That formula doesn't appear to give the quantity values per week though?ValuePerMain = var a = CALCULATE(SUM(Sheet1[Quantity]), REMOVEFILTERS('Sheet1'[Category]),'Sheet1'[Main]=1) RETURN DIVIDE(SUM('Sheet1'[Value]),a,0)Any advice on this?
Rich