cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## 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 Date] , Dates[Date]))

Sum if

Sum If = if    (CALCULATE([Sales£M] , USERELATIONSHIP(Header[Order Date] , Dates[Date]))
>100000
, CALCULATE([Sales£M] , USERELATIONSHIP(Header[Order Date] , Dates[Date])) )

Measure 2

Measure 2 = switch(true() , CALCULATE([Sales£M] , USERELATIONSHIP(Header[Order Date] , Dates[Date]))
> 100000,
CALCULATE([Sales£M] , USERELATIONSHIP(Header[Order Date] , Dates[Date])) )

Thanks for any help on this. Hopefully there is a simple solution

and

Sales£M = sum (SalesDetail[Sales Value £])

3 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team

## Re: DAX using IF. I get an incorrect total.

Hi @RJ

You may refer to below post.

https://community.powerbi.com/t5/Desktop/SUM-Negative-Account-Balances/m-p/544179#M255705

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: DAX using IF. I get an incorrect total.

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 ))

Regular Visitor

## Re: DAX using IF. I get an incorrect total.

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 ) )
8 REPLIES 8
Regular Visitor

## Re: DAX using IF. I get an incorrect total.

Ill try this tomorrow

https://community.powerbi.com/t5/Desktop/DAX-Measure-with-Nested-IF-Statements/m-p/113358/highlight/...

# DAX Measure with Nested IF Statements#

## Re: DAX Measure with Nested IF Statements

[ New ]

@MWinter225If you do want Measures - these should work also!

MEASURE 1

```Total Adj Sales ALT =
SUMX (
'Table',
IF (
'Table'[Sales] * 0.9,
IF ( 'Table'[Adjustment] = "c", 'Table'[sales] * 0.5, 'Table'[Sales] )
)
)```

MEASURE 2 - SWITCH is internally converted into nested IFs - one thing I really like is that its much easier to read and write

```Total Adj Sales ALT 2 =
SUMX (
'Table',
SWITCH (
TRUE (),
'Table'[Adjustment] = "b", 'Table'[Sales] * 0.9,
'Table'[Adjustment] = "c", 'Table'[sales] * 0.5,
'Table'[Sales]
)
)```

Now you have 3 options which should all give you the same result!

Good Luck!

# [

Regular Visitor

## Re: DAX using IF. I get an incorrect total.

No joy with this measure

sumif 2 = sumx('Header' , if( CALCULATE([Sales£M] , USERELATIONSHIP(Header[Order Date] , Dates[Date]))
>100000
, CALCULATE([Sales£M] , USERELATIONSHIP(Header[Order Date] , Dates[Date])) ) )
Super User

## Re: DAX using IF. I get an incorrect total.

Hi,

Does this work?

Community Support Team

## Re: DAX using IF. I get an incorrect total.

Hi @RJ

You may refer to below post.

https://community.powerbi.com/t5/Desktop/SUM-Negative-Account-Balances/m-p/544179#M255705

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: DAX using IF. I get an incorrect total.

Thanks Ashish

I will try this when I return.

Its an aggregation within an aggregation issue but Power BI must have an solution for this. But I will try your measure later

Regular Visitor

## Re: DAX using IF. I get an incorrect total.

Hi Cherie

Thanks

This looks promising.

NegativeSum =

VAR tableA = SUMMARIZE ( Table4, Table4[Client ], "a", CALCULATE ( SUM ( Table4[Amount] ) ) )

RETURN CALCULATE ( SUM ( Table4[Amount] ), FILTER ( tableA, [a] < 0 ) )

Regular Visitor

## Re: DAX using IF. I get an incorrect total.

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 ))

Regular Visitor

## Re: DAX using IF. I get an incorrect total.

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 ) )