cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

 

DateAmountFilter item   
1/1/20201001 Total:610
2/1/20201001   
3/2/2020801   
4/1/2020801   
5/1/2020601   
5/31/2020601   
6/30/2020301   
7/30/2020501   
8/29/2020301   
9/28/2020201   
10/28/202001   
11/27/202001   
12/27/202001   

 

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
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))
totol line.JPG

 

month+total = SUM('Table'[Amount])+[total]

month+total.JPG

 

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.

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

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

@GnortenJones 

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]

jdbuchanan71_0-1594673112236.png

 

Highlighted
Super User IV
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
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))
totol line.JPG

 

month+total = SUM('Table'[Amount])+[total]

month+total.JPG

 

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.

View solution in original post

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.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

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 Kudoed Authors