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
    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
    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 ) )
RJ's avatar
RJ
Icon for Resolver II rankResolver II
7 years ago

Ill try this tomorrow

 

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

 

DAX Measure with Nested IF Statements#

 

Re: DAX Measure with Nested IF Statements

[ New ]
 
Options
 

‎01-14-2017 08:04 PM - edited ‎01-14-2017 08:08 PM

@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's avatar
RJ
Icon for Resolver II rankResolver II
7 years ago
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])) ) )
    • RJ's avatar
      RJ
      Icon for Resolver II rankResolver II
      7 years ago

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

       

       

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