Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Total Sum without Date Affecting when applying to ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

GnortenJones

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-13-2020
01:28 PM

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.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

V-pazhen-msft

Community Support

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-13-2020
11:29 PM

@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
##

jdbuchanan71

Super User IV

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-13-2020
01:45 PM

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

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

TomMartens

Super User IV

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-13-2020
01:48 PM

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

Proud to be a Super User!

I accept Kudos 😉

Hamburg, Germany

Highlighted

V-pazhen-msft

Community Support

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-13-2020
11:29 PM

@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
##

GnortenJones

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-14-2020
06:27 AM

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.

Top Solution Authors

User | Count |
---|---|

407 | |

308 | |

85 | |

55 | |

45 |

Top Kudoed Authors

User | Count |
---|---|

550 | |

482 | |

166 | |

140 | |

122 |