Forum Discussion
DAX cummulative sum by multiple variables
I need to create a new column where it is cummulative sum of sales.
I have following variables:
A: Fiscal year
B: Month
C: Factory
D:: Business
E: Department
I would like to have cummulative sum for sales colum is F using above variables.
could someone help.
Hi Hareensunny
Can you post sample data as text and expected output?
Not enough information to go on;
please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables
Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/- Anonymous2 years ago
Hi Hareensunny ,
Please try to create a new column with below dax formula:
Cummulative Sum = VAR _a = [Column1] VAR _b = [Column2] VAR _c = [Column3] VAR _d = [Column4] VAR _e = [Column5] VAR tmp = FILTER ( 'Table', [Column1] = _a && MONTH ( [Column2] ) = MONTH ( _b ) && [Column3] = _c && [Column4] = _d ) VAR tmp1 = FILTER ( tmp, [Column2] <= _b ) RETURN SUMX ( tmp1, [Column6] )Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- AnonymousNot applicable
Hi Hareensunny ,
Please try to create a new column with below dax formula:
Cummulative Sum = VAR _a = [Column1] VAR _b = [Column2] VAR _c = [Column3] VAR _d = [Column4] VAR _e = [Column5] VAR tmp = FILTER ( 'Table', [Column1] = _a && MONTH ( [Column2] ) = MONTH ( _b ) && [Column3] = _c && [Column4] = _d ) VAR tmp1 = FILTER ( tmp, [Column2] <= _b ) RETURN SUMX ( tmp1, [Column6] )Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. - VahidDMSuper User
Hi Hareensunny
Can you post sample data as text and expected output?
Not enough information to go on;
please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables
Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/