Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |