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.
Fund; Investment; Invested
F1 Inv1 20
F2 Inv1 30
F3 Inv2 40
I want to have a column: Total invested. Which i understood i can get by Calculate(Sum(Invested),Allexcept(table, investment)).
Fund; Investment; Invested; Total invested
F1 Inv1 20 50
F2 Inv1 30 50
F3 Inv2 40 40
However I have a filter on the page to filter Fund the fund, and if i select only F1 and F3 the desired result is
F1 Inv1 20 20
F3 Inv2 40 40
The formula above still gives me 50 for the total invested for F1.
Please can you advise the correct formula to tackle this?
Thanks for your help!
Best regards,
Chris
Solved! Go to Solution.
Hi @klcwgs,
Try this solution with ALLSELECT:
ALLSELECTED = IF ( COUNT ( Table1[Fund] ) = 1, CALCULATE ( SUM ( Table1[Invested] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Investement] = MAX ( Table1[Investement] ) ) ), CALCULATE ( SUM ( Table1[Invested] ), ALLSELECTED ( Table1 ) ) )
The first part with IF is to return the total value if you need to have it in the table, think this works result is below with a slicer but also works with page filters. (ALLEXCEPT is your formula - ALLSELECT is mine)
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @klcwgs,
Try this solution with ALLSELECT:
ALLSELECTED = IF ( COUNT ( Table1[Fund] ) = 1, CALCULATE ( SUM ( Table1[Invested] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Investement] = MAX ( Table1[Investement] ) ) ), CALCULATE ( SUM ( Table1[Invested] ), ALLSELECTED ( Table1 ) ) )
The first part with IF is to return the total value if you need to have it in the table, think this works result is below with a slicer but also works with page filters. (ALLEXCEPT is your formula - ALLSELECT is mine)
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |