How to make a fixed value in the middle of a measure calculation

[Anonymous]

I have a question. I would like to know how to calculate the value of "measure2" in the table below. measure2 is the addition of the aggregated results of measure1. It's easy to calculate as a column, but I want to use a measure to filter dynamically. I tried the following formula, but it doesn't work. The calculation value of measure1 is not fixed to calculate measure2, The result was that the ALL() was overwritten. I want to know the solution.

orijinal table

 I want SUM(Sum_filter1)_groupby_value2

Measure calculation result

Measure expression

SUM(value)_groupby_filter1 = CALCULATE(SUM(Sheet1[value]),ALL(Sheet1[id],Sheet1[filter2]))

SUM(Sum_filter1)_groupby_filter2_a = CALCULATE([SUM(value)_groupby_filter1],ALL(Sheet1[filter1]))

SUM(Sum_filter1)_groupby_filter2_b =
var measure1= CALCULATE(SUM(Sheet1[value]),ALL(Sheet1[id],Sheet1[filter2]))
return
CALCULATE(measure1,ALL(Sheet1[filter1]))

Thank you

[Anonymous · Accepted solution]

Hello @Anonymous 

Following is the DAX Code for the the two measures:-

Measure1 = CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Filter1]))

Measure2 =

VAR M1 =

SUMMARIZE (

'Table',

'Table'[Id],

'Table'[Filter1],

'Table'[Filter2],

'Table'[Value],

"M1", CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Filter1] ) )

)

VAR M2 =

ADDCOLUMNS (

M1,

"M2",

VAR SelectedFilter2 =

SELECTEDVALUE ( 'Table'[Filter2] )

RETURN

SUMX (

FILTER ( ALL ( 'Table' ), 'Table'[Filter2] = SelectedFilter2 ),

CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Filter1] ) )

)

)

RETURN

SUMX( M2, [M2] )

If I solved your query please accept it as a solution.

[Anonymous]

Dear @Anonymous 

I am impressed with what I was thinking!Thank you very much!

In adition ,I have one question as an application of this DAX.

As shown in the table below, is it possible to get the maximum value for each filter1?

I don't want the "id" column to be in the table.

However, it will be blank because it is no longer a unique value.

I am very happy to be able to do this!

Thank you very much.

[Anonymous]

Hello @Anonymous ,

I will have to look into it. As MAX function accepts column reference only. So will have to work on it and as sson as will get the expected result will let you know.

[Anonymous]

Thank you very much. Please let me know if you understand!

[naveenmechu]

Hi,

Create Calculated column instead of first measure and on top of that you can use measure. As below-

SUM(value)_groupby_filter1 = CALCULATE(SUM(Sheet1[value]),ALLEXCEPT(sheet1, sheet1[Filter1]))

SUM(Sum_filter1)_groupby_filter2_a = CALCULATE(sum(sheet1[SUM(value)_groupby_filter1]),ALLEXCEPT(sheet1, Sheet1[filter2]))

Note:- First one is calculated column and second is measure.

Hope it will help you

Thanks 

Naveen