Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
I 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
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
Hi 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
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.
Proud to be a Super User!
Hi Kcantor,
I don't know if I well undestood but for the others portfolios (different to 77) I'd like to have the sum of values of the portfolio 77 (for each Isin Code).
I don't know if with your solution I will have the desidered result.
I will try and I will give you a feedback.
Thank you
Lara
@larabraghetti Is this what you need? I used Sum = SUM(Table1_2[Value]) and then used the N. Por on my rows
Proud to be a Super User!
Here's a Matix that will show for each Isin Code the N. Por and the N 77 Total
So you get your N 77 Total within each Isin Code and will show that Total for All N. Por within each Isin Code
You
You can also see the values in the same Matrix - but you'll have to turn off totals!
Thanks to @Sean @kcantor @nikil for the answers.
@Sean your solution is ok, but I need this result as a column in my table in PowerBI because the game is a little bit more complex (previously I put a semplified example) and I need the calculated column in order to built others columns (so I can't use filters or matrix) .
I apologize for my inexperience but I'm a beginner in DAX and PowerBI.
I'll try to explain you better what is my aim, step by step.
This is my real DB in Excel:
In PowerBI I'd like to built a column that give me a result like the following done in excel:
But in PowerBI I obtain this result:
The aim is to have a conversion factor (based on the ptf 77) that allows me to reallocate value of portfolios different to 77 based on portfolio 77.
Thank you
Regards
Lara
Any idea?
I'm blocked on this topic...
Build the column as you would if the row only exists one time. Then, when pulling it into PowerBi visualizations, use the portfolio/isin/ column labels as your axis or row on the matrix. Just use the column headings instead of the cell references you would in excel. You may initially get an error about no row reference existing but, often times, this is overcome when you add it to the visualization.
Unfortunately, I am not able to see the images you are sharing today as I am on my mobile but the gist of what I am reading is that you want a specific column calculation that can be combined based on the portfolio. The visualization rows/axis will handle that part.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |