Forum Discussion

RJ's avatar
RJ
Icon for Resolver II rankResolver II
7 years ago
Solved

DAX using IF (aggregation within another aggregation). I get an incorrect total.

  What I want is the total to equal 2,947,891 for Sumif and Measure 2   The three measures were   Sales£OrdDate2   Sales£OrdDate2 = CALCULATE([Sales£M] , USERELATIONSHIP(Header[Order Da...
Need Help
RJ's avatar
RJ
Icon for Resolver II rankResolver II
7 years ago

Brilliant

 

It worked. Thank you

 

Sumif4 =
VAR tableA = summarize(Dates,Dates[MonthYear],"a",
CALCULATE([Sales£M] ,USERELATIONSHIP(Header[Order Date] ,Dates[Date] )) )
 
RETURN
CALCULATE ( [Sales£M] ,USERELATIONSHIP(Header[Order Date] ,Dates[Date] ) , FILTER ( tableA, [a] > 100000 ) )
 
//-----------------------
 
So a variable is set up called VAR tableA
This generates a temp table summarized by a column Dates[MonthYear]. With the measure 
CALCULATE([Sales£M] ,USERELATIONSHIP(Header[Order Date] ,Dates[Date] ))
 
So in effect a  temporary table is created. This table is called TableA. It can be called any name. This temp table has one dimension >>Dates,Dates[MonthYear]  << and one measure called a. It could however be called any name required
 
This temp table is then used to filter the expression 
CALCULATE ( [Sales£M] ,USERELATIONSHIP(Header[Order Date] ,Dates[Date] )
as required
 
NB. Another option is to create a new actual table. Say
 
TableName =
SalesMthYrOrdDate = ADDCOLUMNS( summarize(Dates, Dates[MonthYear],Dates[Year]),   //load required dimensions
"SalesOrdDate",                                //or whatever name you wish to call the measure
CALCULATE([Sales£M] ,USERELATIONSHIP(Header[Order Date] ,Dates[Date] )) )   //measure column called "SalesOrdDate"
 
and then join the MonthYear column to the MonthYear column in the Dates table //link using PBI relationships
 
Final step. Use this expression
 
Sumif7 =
CALCULATE ( [Sales£M] ,USERELATIONSHIP(Header[Order Date] ,Dates[Date] ) , FILTER (SalesMthYrOrdDate,SalesMthYrOrdDate[SalesOrdDate] > 100000 ))
 
RJ's avatar
RJ
Icon for Resolver II rankResolver II
7 years ago

To make it more effecient. Add 'ADDCOLUMN'

 

Sumif6 =
VAR tableB = ADDCOLUMNS( summarize(Dates, Dates[MonthYear],Dates[Year]), "SumMthYr",CALCULATE([Sales£M] ,USERELATIONSHIP(Header[Order Date] ,Dates[Date] )) )
RETURN CALCULATE ( [Sales£M] ,USERELATIONSHIP(Header[Order Date] ,Dates[Date] ) , FILTER ( tableB, [SumMthYr] > 100000 ) )
 
 
 
or an alternative is
 
Sumif6 =
VAR tableB = summarizecolumns(Dates, Dates[MonthYear],Dates[Year], "SumMthYr",CALCULATE([Sales£M] ,USERELATIONSHIP(Header[Order Date] ,Dates[Date] )) )
RETURN CALCULATE ( [Sales£M] ,USERELATIONSHIP(Header[Order Date] ,Dates[Date] ) , FILTER ( tableB, [SumMthYr] > 100000 ) )