Forum Discussion
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.
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))
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
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
- calculate allows to alter an existing filter context, the first parameter a numeric expression, also a measure can be used.
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]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))
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.
