Forum Discussion
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...
Hi RJ
You may refer to below post.
https://community.powerbi.com/t5/Desktop/SUM-Negative-Account-Balances/m-p/544179#M255705
Regards,
Cherie
Brilliant
It worked. Thank you
Sumif4 =VAR tableA = summarize(Dates,Dates[MonthYear],"a",CALCULATE([Sales£M] ,USERELATIONSHIP(Header[Order Date] ,Dates[Date] )) )RETURNCALCULATE ( [Sales£M] ,USERELATIONSHIP(Header[Order Date] ,Dates[Date] ) , FILTER ( tableA, [a] > 100000 ) )//-----------------------So a variable is set up called VAR tableAThis generates a temp table summarized by a column Dates[MonthYear]. With the measureCALCULATE([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 requiredThis temp table is then used to filter the expressionCALCULATE ( [Sales£M] ,USERELATIONSHIP(Header[Order Date] ,Dates[Date] )as requiredNB. Another option is to create a new actual table. SayTableName =SalesMthYrOrdDate = ADDCOLUMNS( summarize(Dates, Dates[MonthYear],Dates[Year]), //load required dimensions"SalesOrdDate", //or whatever name you wish to call the measureCALCULATE([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 relationshipsFinal step. Use this expressionSumif7 =CALCULATE ( [Sales£M] ,USERELATIONSHIP(Header[Order Date] ,Dates[Date] ) , FILTER (SalesMthYrOrdDate,SalesMthYrOrdDate[SalesOrdDate] > 100000 ))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 isSumif6 =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 ) )
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 ))
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 ) )