Subtraction of a measure subtotal in other measure

[ivvan_smo]

Hi all, 

can you please help with creation of a measure, which subtracts a subtotal value of a previous measure from the newly created one in DAX (will be used in power pivot)? 

Something like this (I need to create Measure 2): 

*Measure one subtotal is an average of the values above)

Thanks a lot, 

I.

[ValtteriN]

Hi,

Here is one way to do this:

Data:

Measure for average of all:

Measure 25 = CALCULATE(AVERAGE('Table (15)'[Value2]),ALL('Table (15)'))

Measure for selected row value - average:

Measure 26 = SELECTEDVALUE('Table (15)'[Value])-[Measure 25]

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[ivvan_smo]

Hi @ValtteriN , 

thanks a lot, in this simplified example this indeed help. 

Do you have some suggestion how to do this also in more complicated data set with weighted average? 

For example: 

Thank you!

I.

[ValtteriN · Accepted solution]

Hi,

I am not sure I follow, but I guess here you would want to calculate the "Diff vs avg"? The logic is excatly the same in that you can use ALL to calculate the 12.8% value and then it is a simple case of substracting that from SELECTEDVALUE(table[TGT profit]). 


E.g.

weighted average =

var _totalW = CALCULATE(SUM('Table (16)'[weight]),ALL('Table (16)')) return

SUMX(ALL('Table (16)'),'Table (16)'[weight]*'Table (16)'[Column1]/_totalW)

Measure 28 = IF(ISBLANK(SELECTEDVALUE('Table (16)'[tgt])),

SUMX('Table (16)','Table (16)'[tgt]-[weighted average]), //total row

SELECTEDVALUE('Table (16)'[tgt])-[weighted average]) //other rows

End result: