Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

f_999_0-1641630389354.png

 I want SUM(Sum_filter1)_groupby_value2

f_999_1-1641630439717.png

Measure calculation result

f_999_2-1641630821297.png

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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
Not applicable

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.

f_999_4-1641882497371.png

f_999_0-1641882809182.png

 

 

f_999_2-1641882184992.png

 

 

Anonymous
Not applicable

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
Not applicable

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

naveenmechu
Helper I
Helper I

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.
 
naveenmechu_0-1641655133735.png

 



Hope it will help you
 
Thanks 
Naveen

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.