Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
V-pazhen-msft
Community Support
Community Support

@Anonymous 
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
V-pazhen-msft
Community Support
Community Support

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

Anonymous
Not applicable

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.

TomMartens
Super User
Super User

hey @Anonymous ,

 

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

@Anonymous 

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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