cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Total Sum without Date Affecting when applying to visualization/Measure

I feel dumb, because I know I'm overlooking something obvious.  I need to write a measure that will return a total value, but won't change from total value to monthly value, when I apply it to a graph or another meaure.

I have a data set, based on months, and monthly amounts, that I need to calculate the total of these amounts based on one filter (Filter Item "1" on my example below).  The total amount in this example is 610.

 Date Amount Filter item 1/1/2020 100 1 Total: 610 2/1/2020 100 1 3/2/2020 80 1 4/1/2020 80 1 5/1/2020 60 1 5/31/2020 60 1 6/30/2020 30 1 7/30/2020 50 1 8/29/2020 30 1 9/28/2020 20 1 10/28/2020 0 1 11/27/2020 0 1 12/27/2020 0 1

I need to do two things:

1) on a line graph I'm tracking my monthly/yearly amounts, but I want to have a straight line at the total, so I can show when other measurements go above that total.

2)  I need to be able to add that total value (610) to other measures that calculate how much above that amount we go over.  So "Monthly amount" + "610" each month.

To do this I need to get that 610 value but everytime I try to apply my total calculation, it's giving me the Monthly total, not the entire total.

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Community Support

## Re: Total Sum without Date Affecting when applying to visualization/Measure

@GnortenJones
If I understand you correctly, you can create 2 measures for each requirement:‘

total = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Filter item]=1)) month+total = SUM('Table'[Amount])+[total] Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4
Highlighted Super User IV

## Re: Total Sum without Date Affecting when applying to visualization/Measure

First a measure to just sum the amounts

``Amount Sum = SUM ( 'Table'[Amount] )``

Then a measure for the Total Amount

``Total Amount = CALCULATE ( [Amount Sum], ALL ( 'Table' ))``

Then we can combined them.

``Total + Month = [Amount Sum] + [Total Amount]`` Highlighted Super User IV

## Re: Total Sum without Date Affecting when applying to visualization/Measure

hey @GnortenJones ,

give this measure a try:

``````measurename =
calculate(
sum('<tablename>'[Amount])
, ALL('<tablename>'[Date])
, '<tablename>'[filter item] = 1
)
``````
• calculate allows to alter an existing filter context, the first parameter a numeric expression, also a measure can be used.
calculate has 1 to n parameters, 2 to n allow to modify an existing filter context
• ALL removes (to be technically correct: blocks) filter that will be simplicity applied , e.g. axis label, slicer selections, row/column header
• the 3rd parameter just filters for filter item equals 1, no matter if the column is filtered by another value

Hopefully, this helps to tackle your challenge.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted Community Support

## Re: Total Sum without Date Affecting when applying to visualization/Measure

@GnortenJones
If I understand you correctly, you can create 2 measures for each requirement:‘

total = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Filter item]=1)) month+total = SUM('Table'[Amount])+[total] Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Frequent Visitor

## Re: Total Sum without Date Affecting when applying to visualization/Measure

This did it.  This was the approach I was taking in the beginning that wasn't working.  The only difference is I didn't have the "All" statement in the Filter for the "total" function.

I knew I was overlooking something simple.

Thanks.

Announcements #### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge! #### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event. Top Solution Authors
Top Kudoed Authors
Users online (901)