Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

sum up value based on category

hi guys, please help with the problem statement : the data is categorized by fiscal period but currently i want the DAX to calculate the sum of sales per month on the "sum up sales per month" column, how can i write the DAX for this?

 

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous 

    Create a measure like this :

    Sum up sales per month = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[month in quarter]))

    You will get a result is as follows :

    I have attached my pbix file , you can refer to it .

     

    Best Regards,
    Community Support Team _ Ailsa Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , A measure like

    Sumx(filter(allselected(Table), Table[Fiscal Period] = max(Table[Fiscal Period])),Table[Sales])

     

    new column

    Sumx(filter((Table), Table[Fiscal Period] = earlier(Table[Fiscal Period])),Table[Sales])

     

     

    Better to have separate FY Table
    calculate(Sum(Table[Sales]),filter(allselected(FY), FY[Fiscal Period] = max(FY[Fiscal Period])))

     

    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous 
    You can use 

    sum up sales per month =
SUMX (
    CALCULATETABLE ( Sales, ALLEXCEPT ( Sales, Sales[Fiscal Period] ) ),
    Sales[Sales]
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous 
    you can make a Summary Table like 

    Summary Table =
SUMMARIZE(Table,fiscal period,month in quarter,"Sum up sales per month",SUM(Sales))
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous 

    Create a measure like this :

    Sum up sales per month = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[month in quarter]))

    You will get a result is as follows :

    I have attached my pbix file , you can refer to it .

     

    Best Regards,
    Community Support Team _ Ailsa Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.