Forum Discussion

Hareensunny's avatar
Hareensunny
Frequent Visitor
2 years ago
Solved

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/

  • Anonymous's avatar
    Anonymous
    2 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

  • Anonymous's avatar
    Anonymous
    Not 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.

  • 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/