Forum Discussion
aggregation
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
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
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