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