Forum Discussion
Helper IIDAX: sum with filters
Dear all,
I need some help in order to built a formula in my DB.
My table "Pos" is structed in this way:
Column: Portfolio, Isin Code, Value
I'd like to have a sum of Value for each Isin code and Ptf=77.
I've built a formula that works but only for the lines with Ptf=77 but I need to replicate the same value for all the portfolios (not only for 77).
This is my formula:
Wrong result: CALCULATE(SUM(Pos[Value]);Pos[Isin Code];Pos[N. Por]=77)
Is there someone that could help me?
Thank you in advance
Lara
13 Replies
- nikil
Resolver I
Following DAX measure should work for you:
Result = CALCULATE(SUM('Pos'[Value]),ALL('Pos'[Isin Code]),'Pos'[N.Por]=77)
Check out the Chicagoland Power BI User Group
larabraghettiHi Nikil,
thanks for your answer but I still have the same issue... I see the Result only for lines with portfolio 77 and not for the others ptf.
Regards
Lara- kcantor
Community Champion
Use just a simple SUM as in =SUM('Pos'[Value]). then apply the filters by portfolio either to the visual or as a slicer. For example, use the portfolio as the rows in a matrix with the sum to the side. This will use the calculation and apply it across all porfolios. You can also use the portfolio as an axis.
By wrapping the sum in a calculate and specifing portfolio 77, you are having the calculation ignore all other portfolios. That is fine if you only want to use the one portfolio but if you want others you need to let the slicers work for you. Otherwise you have to create a calculation for each portfolio and use them individually.
SeanI don't know if you can even write a Measure that can accomplish this?
For each UNIQUE Isin Code - SUM all Values that have N. Por 77
Measure = CALCULATE (SUM(Pos[Value], Pos[Isin Code]=Each UNIQUE Isin Code, Pos[N. Por]=77)
EDIT: Your best bet is what kcantor suggests - use the Filters
