Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
larabraghetti
Helper II
Helper II

DAX: 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)

Cattura.JPG

 

 

Is there someone that could help me?
Thank you in advance

Lara

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

13 REPLIES 13
Sean
Community Champion
Community Champion

@larabraghetti

 

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

 

 

nikil
Resolver I
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 

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

@larabraghetti

 

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.





Did I answer your question? Mark my post as a solution!

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

isin.JPG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

@larabraghetti

 

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

 

Dax with Filters.png

 

You

Sean
Community Champion
Community Champion

You can also see the values in the same Matrix - but you'll have to turn off totals!

Dax with Filters2.png

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:

Excel base.JPG

In PowerBI I'd like to built a column that give me a result like the following done in excel:

Excel.JPG

But in PowerBI I obtain this result:

PowerBI desktop.JPG

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

@larabraghetti I think we got it!

 

Solution AE.png

 

Thank you very much @Sean!!!

You saved me!

Bye
Lara

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.