Forum Discussion

saranee's avatar
saranee
Icon for Helper I rankHelper I
8 years ago
Solved

Sum used in expression for sumx

Hi,

 

We have a dax function Sumx(Table,sum(SALARY)) We are getting like sum of salary for individual country for each row and then multiplied by each times the country appeared in the table.For e.g for UK ,Salary (20+30)*2=100 and getting that as an individual row.I am not getting what exactly is happening in this calculation

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

But when we are using dax function Sumx(Table,calculate(sum(SALARY))) then we are getting simple sum grouped by distinct country like below.Please can anyone help me with this.

 

 

Thanks,

Saranee

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi saranee

     

    This is one of the features of CALCULATE.. it transforms ROW CONTEXT into FILTER CONTEXT

     

    To test it....just add these two simple calculated columns in your above TABLE... Both will give different results

     

    Column1 =sum('Table'[Salary])
    Column2=CALCULATE(sum('Table'[Salary]))

     



    Simialry...inside an ITERATOR.... these two expressions behave differently

     

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Hi saranee

     

    Actually SUMX is an iterator. Iterators provide ROW context but not the filter context.....

     

    So it behaves like a calculated column....i.e when you write SUM(Column) in a calculated column ...it sums the entire Table without taking into account filters like Row Filters, Column Filters, Slicers.

     

    But when you wrap it inside CALCULATE... this ROW context is transformed into FILTER context

    • saranee's avatar
      saranee
      Icon for Helper I rankHelper I
      8 years ago

      Thanks Zubair for explanation but when I am using Calculate I am not using any filter in it,Sorry I am not having much idea about dax.

      Thanks,

      Saranee

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        Hi saranee

         

        This is one of the features of CALCULATE.. it transforms ROW CONTEXT into FILTER CONTEXT

         

        To test it....just add these two simple calculated columns in your above TABLE... Both will give different results

         

        Column1 =sum('Table'[Salary])
        Column2=CALCULATE(sum('Table'[Salary]))

         



        Simialry...inside an ITERATOR.... these two expressions behave differently