Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Creating dynamic sum subtotal in column function

Hello Power BI comunity,    I'm struggling with creating a dynamic subtotal sum in a custom column. The basic request is to have a subtotal sum which changes when the user applies a filter or slice...
Need Help
v-xicai's avatar
v-xicai
Icon for Community Support rankCommunity Support
7 years ago

Hi Anonymous ,

 

You may create column like DAX below.

 

Sub total= CALCULATE(SUM(Table1[Sold]),FILTER(ALLSELECTED(Table1), Table1[Items] =EARLIER(Table1[Items])))

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks Amy, 

     

    However it is still not my desired outcome.

     

    The subtotal should be 810 while no slicer is selected, however when a user applies a filter it should create a sum of the seleceted. 

     

     

     

     

     

     

    The regular sum function reduces the number of row however, the value remains static at the 810. My request is that the subtotal becomes 600 in this case where item 2 and 3 is selected. 

    Best regards

    Lasse 

     

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        No, I used the function given by Amy: 

         

        Subtotal = CALCULATE(SUM('Table'[Sold]),FILTER(ALLSELECTED('Table'),'Table'[Items]=EARLIER('Table'[Items])))
         
        This what the data column looks like. 

        The 285 is basically the sum of item 2 times 3 becasue it occurs three times.