Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
ARob198
Helper IV
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:

 

NameDateName ValueFund Name
ACJan-20       10.00A
AFJan-20         2.00A
AWJan-20         1.00A
BCJan-20       14.00B
BEJan-20         5.30B
BFJan-20         6.00B
ACFeb-20       10.50A
AFFeb-20         2.10A
AWFeb-20         1.05A
BCFeb-20       14.70B
BEFeb-20         5.57B
BFFeb-20         6.30B
ACMar-20       10.71A
AFMar-20         2.14A
AWMar-20         1.07A
BCMar-20       14.89B
BEMar-20         5.64B
BFMar-20         6.38B
ACApr-20       10.50A
AFApr-20         2.10A
AWApr-20         1.05A
BCApr-20       14.59B
BEApr-20         5.52B
BFApr-20         6.25B

 

I would like the result to be:

 

TotalValueDateFund Name
       13.00Jan-20A
       25.30Jan-20B
       13.65Feb-20A
       26.57Feb-20B
       13.92Mar-20A
       26.91Mar-20B
       13.64Apr-20A
       26.37Apr-20B
8 REPLIES 8
dax
Community Support
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)

Please do mask sensitive data before uploading.

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.

az38
Community Champion
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
LinkedIn

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". 

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

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])
         )

 

Funds Summary.jpg

 

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])

amitchandak
Super User
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

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...

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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