Forum Discussion

richard_jackson's avatar
richard_jackson
Frequent Visitor
3 years ago
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...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

     

     

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
3 years ago

something like this?

You would also have to specify how the totals should be computed.

 

richard_jackson's avatar
richard_jackson
Frequent Visitor
3 years ago

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