aggregation

[Anonymous]

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 result . so with this two column i want aggregate the market values 

so i have written the below dax, which is taking so much time 

Measure 21 = CALCULATE(sum(POSITIONS[MARKET_VALUE_TRADE_DATE_BASE]),ALLEXCEPT(POSITIONS,POSITIONS[col_act],POSITIONS[Col_111]))

can you correct if i have written any error.

[lc_finance]

Hi @Anonymous ,

can you share a sample Power BI file?

You can share it via via DropBox, One Drive, Drive or other similar tool.

Also an example of the table you want to have

Best regards

LC

[Anonymous]

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]

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]

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]

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]

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]

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

Partition	MARKET_VALUE_TRADE_DATE_BASE
col_11	100
col_11	120
col_22	80
col_22	50
col_22	60
col_33	70

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:

Partition	Measure
col_11	45.8
col_22	39.6
col_33	14.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