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

     

     

  • richard_jackson's avatar
    richard_jackson
    Frequent Visitor
    3 years ago

     

    Hi lbendlin - thanks for that!

     

    Data set

     

    WeekCategoryMainOtherQuantityValue
    1A1021
    1B0132
    1B0143
    1C0154
    2A1065
    2B0176
    2C0187

     

    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)

     

    WeekCategoryValueValue/MainQuantity
    1B52.5
    1C42

     

    Any guidance would be greatly appreciated!

     

    Thanks!

     

    Rich

    • 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