Creating dynamic sum subtotal in column function

[Anonymous]

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

Items	Sold	Sum	Sub total
Item 1	10	270	270
Item 2	20	270	270
Item 3	30	270	270
Item 3	40	270	270
Item 2	50	270	270
Item 1	15	270	270
Item 2	25	270	270
Item 3	35	270	270
Item 1	45	270	270

Desired outcome (Applied slicer):

Items	Sold	Sum	Sub total
Item 1	10	270	70
Item 1	15	270	70
Item 1	45	270	70

Actual outcome (Applied slicer):

Items	Sold	Sum	Sub total
Item 1	10	270	270
Item 1	15	270	270
Item 1	45	270	270

Thanks in advance

[v-xicai]

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]

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 

[Greg_Deckler]

Where are those numbers coming from for the subtotal in the rows? Item 2, 285?

Is this a measure totals problem? If so, Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[Anonymous]

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.   

[v-xicai]

Hi @Anonymous ,

When I use the formula above, the Subtotal will return the result 600 instead of 800. I am not sure what desired result would you want, could you please show your desired output screenshots for further analysis?

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]

 Hi Amy, 

This is my desired outcome:

When i filter to see 'Item 2' and 'Item 3' then i summerize in a subtotal. 

Or 'Item 1' and 'Item 3'. 

It is used to achive a dynamic sum the items should be divided with in the end. 

Do you understand my desired outcome?

Best regads

Lasse 

[Anonymous]

Has anyone found a solution yet?

[Greg_Deckler]

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]

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'.