Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 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 slicer. The functionality is known from Excel however, it has not been possible for me to find the function in Power BI.

 

Does anyone have some experience in creating this function?

 

Data sheet (simplified):

ItemsSoldSumSub total
Item 110270270
Item 220270270
Item 330270270
Item 340270270
Item 250270270
Item 115270270
Item 225270270
Item 335270270
Item 145270270

 

Desired outcome (Applied slicer):

ItemsSoldSumSub total
Item 11027070
Item 11527070
Item 14527070

 

Actual outcome (Applied slicer):

ItemsSoldSumSub total
Item 110270270
Item 115270270
Item 145270270

 

Thanks in advance

Need Help

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Seems like you want something like this measure:

     

    Measure = 
var __item = MAX([Items])
var __table = FILTER(ALL('Table'),[Items] = __item)
RETURN
SUMX(__table,[Sold])
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for your answer

       

      Me issue is though I want to build it into a custom column, since i want to divide every single row/value with the 'total sum'.

  • v-xicai's avatar
    v-xicai
    Community Support
    6 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
      6 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