cancel
Showing results for
Did you mean:

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.

Helper IV

## Calculate sum by month and second filter

Hello,  I am trying to use calculate with a filter to perform the equvalent of the SUMIFS function in excel.  I am not sure why this is not working for me.  I have data by date that I would like to sum by date and by Fund Name. Is anyone able to help?  Is calculate the right function to do this?

Thank you!

Here is my example:

 Name Date Name Value Fund Name AC Jan-20 10.00 A AF Jan-20 2.00 A AW Jan-20 1.00 A BC Jan-20 14.00 B BE Jan-20 5.30 B BF Jan-20 6.00 B AC Feb-20 10.50 A AF Feb-20 2.10 A AW Feb-20 1.05 A BC Feb-20 14.70 B BE Feb-20 5.57 B BF Feb-20 6.30 B AC Mar-20 10.71 A AF Mar-20 2.14 A AW Mar-20 1.07 A BC Mar-20 14.89 B BE Mar-20 5.64 B BF Mar-20 6.38 B AC Apr-20 10.50 A AF Apr-20 2.10 A AW Apr-20 1.05 A BC Apr-20 14.59 B BE Apr-20 5.52 B BF Apr-20 6.25 B

I would like the result to be:

 TotalValue Date Fund Name 13.00 Jan-20 A 25.30 Jan-20 B 13.65 Feb-20 A 26.57 Feb-20 B 13.92 Mar-20 A 26.91 Mar-20 B 13.64 Apr-20 A 26.37 Apr-20 B
8 REPLIES 8
Community Support

Hi @ARob198 ,

I am not clear about your requirement, if you want to get this in column , you could try to create calculated column like below

``Column = CALCULATE(SUM('Table'[Name Value]), FILTER('Table','Table'[Date]=EARLIER('Table'[Date])&& 'Table'[Fund Name]=EARLIER('Table'[Fund Name])))``

or create a table like below

``Table 2 = SUMMARIZE('Table', 'Table'[Date], 'Table'[Fund Name], "Fund Month Total" , SUM('Table'[Name Value]))``

You could refer to my sample for details. If this is not waht you want, please correct me and inform me more detailed information (such as your sample and your expected output)

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Champion

Hi @ARob198

use ALLEXCEPT(),like

``Column or Measure = CALCULATE(SUM(Table[TotalValue]), ALLEXCEPT(Table, Table[Fund Name], Table[Date]) )``

do not hesitate to give a kudo to useful posts and mark solutions as solution
Helper IV

No- that doesn't work because the total value column is what I am trying to find the value of.  It cannot be an input.  Maybe I am not modifying your calculation right...but all this does is return the value that I already have and does not sum anything by fund name.  Is there no such thing as sumifs in dax?  I just want a column of numbers to be summed if two criteria are met.  Criteria #1 is by date (but I want the date to be dynamic, not specified by a specific date) and Criteria 2 is the fund name which is associated with the value as another column (There are many names so again I do want this to be dynamic, not specified with a = "specific value".

Resolver II

This should work. Let me know. Thanks

FundMonthTotal =
VAR Fund = VALUES(Fund_Summary[Fund Name])
VAR YearMonth = VALUES(Fund_Summary[Date])VAR FundMonthTotal =
CALCULATE (
SUM (Fund_Summary[Name Value] ),
FILTER (
Fund_Summary,
Fund_Summary[Fund Name] = Fund && Fund_Summary[Date] = YearMonth
)
)
RETURN
FundMonthTotal

Resolver II

My apologies - I posted too quickly. Use this approach instead.

In query manager, duplicate the fact table and keep only the unique combinations of Fund Name and Date.

Use the DAX below to add a calculated column to this new table.

FundMonthTotal =
CALCULATE (
[ValueTotal],
FILTER (
RELATEDTABLE(Fund_Summary),
Fund_Summary[Fund Name] = Fund_Month_Totals[Fund Name]

&& Fund_Summary[Date] = Fund_Month_Totals[Date])
)

Resolver II

I thought about this some more and realized the exact results can be obtained more efficiently using SUMMARIZE to create the second table...

FundSummaryMonthTotal =
SUMMARIZE(Fund_Summary, Fund_Summary[Date], Fund_Summary[Fund Name], "Fund Month Total" , Fund_Summary[ValueTotal])

Super User

@ARob198 , Put it in a visual  like table or matrix and take sum of Name value and Take Date and fund name as un summarized or on row

Helper IV

What if I want it to be a value in a column?  I need to do further calculations off of this value.  Really as an intermediate step to sovle the quesiton a posted a few days ago.  Will I still be able to use it for calculations if I do this?

https://community.powerbi.com/t5/Desktop/First-of-Month-End-of-the-Month-Cap-Table/m-p/1056921#M4941...

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors