Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

aggregation

Hi All,   I have a position table, in this position table   i have created two new columns as col_act (dax as related(acct_num)) and Col_111(dax as related(port_num)) .. i was able to get correct r...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

i m working in high secured place .. so i cannot give u the file .. 

 

i want aggegation of market vakues by fileds which are from differnet tables 

 

suppose , i have 3 tables .. table1 has acct_num , table2 has port_num and table 3 has market_value. now in dax, i need write a expression to aggegate the market_value group by acct_num,port_num

lc_finance's avatar
lc_finance
Solution Sage
6 years ago

Hi Anonymous ,

 

 

if you create a connection between the 3 tables, then you can visualize market_value by acct_num,port_num even without DAX.

Or you already have a connection between the tables?

 

Luca

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Already three tables have relationship between them . i looking for dax.. so how can we do aggregation from differnt tables.

     

    i have already created columns using related function to pull these two fields in position table. 

     

    Below is the dax i have written .. plz correct me if i m worng 

    CALCULATE(sum(POSITIONS[MARKET_VALUE_TRADE_DATE_BASE]),ALLEXCEPT(POSITIONS,POSITIONS[Col_11],POSITIONS[Col_22]))

     

    • lc_finance's avatar
      lc_finance
      Solution Sage
      6 years ago

      Hi Anonymous ,

       

      Your formula below will sum up the column 'MARKET_VALUE_TRADE_DATE_BASE', for all rows of the POSITIONS table with the exception of filters that might be on Col_11 or Col_22.

      Is this what you are trying to do?

       

      Definitely seeing your PBI file would help.. maybe you could replace Customer names with codes? or change the amounts? so it becomes less confidential

       

      Best of luck,

       

      LC

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        i m bit weak on DAX side . so i have written that code..

         

        my requirement is 

        below is the code from SQL .. i need write the same in dax side.

         

        sum(totalmarketvalue) over (partition by col_11, col_22, col_33)
        /
        sum(totalmarketvalue) over () * 100