topic Re: sum up value based on category in DAX Commands and Tips

sum up value based on category

Anonymous — Thu, 24 Mar 2022 05:05:10 GMT

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?

Re: sum up value based on category

amitchandak — Thu, 24 Mar 2022 05:24:48 GMT

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

Re: sum up value based on category

tamerj1 — Thu, 24 Mar 2022 06:31:37 GMT

Hi Anonymous
You can use

sum up sales per month = SUMX ( CALCULATETABLE ( Sales, ALLEXCEPT ( Sales, Sales[Fiscal Period] ) ), Sales[Sales] )

Re: sum up value based on category

Anonymous — Thu, 24 Mar 2022 09:49:50 GMT

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))

Re: sum up value based on category

Anonymous — Tue, 29 Mar 2022 06:07:13 GMT

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.