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

      • lc_finance's avatar
        lc_finance
        Solution Sage
        6 years ago

        Hi Anonymous ,

         

         

        It's still not very clear to me what your data model looks like. I make the hypothesis that it looks as follow:

         

        Table: POSITION

        PartitionMARKET_VALUE_TRADE_DATE_BASE
        col_11100
        col_11120
        col_2280
        col_2250
        col_2260
        col_3370

         

        In that case, I would recommend the following formula:

         

        Measure = (
CALCULATE(sum(POSITIONS[MARKET_VALUE_TRADE_DATE_BASE]),ALLEXCEPT(POSITIONS,POSITIONS[Partition])) /
CALCULATE(sum(POSITIONS[MARKET_VALUE_TRADE_DATE_BASE]),ALL(POSITIONS)) 
) *100

        Here is the result of the measure based on my hypothesis table above:

        PartitionMeasure
        col_1145.8
        col_2239.6
        col_3314.6

         

        It means that the market value of col_11 corresponds to 45.8% of the total market value, the market value of col_22 corresponds to 39.6% of total market value, etc

         

        I hope this is what you are looking for and it helps you!

        If not, please consider sharing a sample Power BI, I would not know how to help further with it.

         

        Regards,

         

        LC

        Interested in learning Power BI and DAX? Check out my blog at www.finance-bi.com