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
RJ
Resolver II
Resolver II

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

PBITemp.JPG

 

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

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.

View solution in original post

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

View solution in original post

RJ
Resolver II
Resolver II

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

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

 

Does this work?

 

=CALCULATE(SUMX(VALUES(Calendar[Month-Year]),[Sales£M]),FILTER(Header,[Sales£M]>0),USERELATIONSHIP(Header[Order Date] , Dates[Date]))


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

RJ
Resolver II
Resolver II

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! Smiley Happy

MEASURE 1

Total Adj Sales ALT =
SUMX (
    'Table',
    IF (
        'Table'[Adjustment] = "b",
        '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! Smiley Happy

[

RJ
Resolver II
Resolver II

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

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.

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
Resolver II
Resolver II

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

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

 

 

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.