Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
@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))
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
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))
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.
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.
hey @Anonymous ,
give this measure a try:
measurename =
calculate(
sum('<tablename>'[Amount])
, ALL('<tablename>'[Date])
, '<tablename>'[filter item] = 1
)
Hopefully, this helps to tackle your challenge.
Regards,
Tom
@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]
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |