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.
Hello Community,
I am trying to calculate the running total of a value based on 2 different columns, date and type. I have a measure that does this successfully for 2 different values, however, now I need to be able to compare the those 2 running totals to find which RT of value 2 is the closest to value 1. The problem that I have encountered is that I cannot use the MAX function on a measure, so I have been trying to recreate my running total on a calculated column to achieve what I need.
The measures I have that work perfectly right now are:
RT Value1:= IF(SELECTEDVALUE(Table[DATE])
> calculate(
LASTNONBLANK(Table[DATE], 1),
FILTER(all(Table),Table[Value1] <> 0)),BLANK(),
CALCULATE(
SUM('Table'[Value1]),
FILTER( ALLSELECTED('Table'[DATE]), 'Table'[DATE] <= MAX(Table[DATE])
)
))
RT Value2:= CALCULATE(
SUM('Table'[Value2]),
FILTER(
ALLSELECTED('Table'[DATE]),
ISONORAFTER('Table'[DATE], MAX('Table'[DATE]), DESC)
)
)
This is the formula I am using for my Calculated Column that is not working:
RT Value1 Calc Column = IF(SELECTEDVALUE(Table[DATE])
> calculate(
LASTNONBLANK(Table[DATE], 1),
FILTER(all(Table),Table[Value1] <> 0)),BLANK(),
CALCULATE(SUM(Table[Value1]),FILTER(ALLEXCEPT(Table,Table[Type]), Table[DATE] <= MAX(Table[DATE]))))
Here is an example of my dataset:
Date | Value1 | RT Value1 | RT Value1 Calc Column |
6/30/2020 | $0 | $0 | $5,216,939 |
7/31/2020 | $18,322 | $18,322 | $31,301,637 |
8/31/2020 | ($13,669) | $4,653 | $32,692,821 |
9/30/2020 | $45,317 | $49,970 | $50,082,619 |
10/31/2020 | $49,553 | $99,523 | $49,734,823 |
11/30/2020 | $27,554 | $127,077 | $43,474,495 |
12/31/2020 | $120,843 | $247,920 | $44,170,087 |
1/31/2021 | $27,568 | $275,488 | $49,387,027 |
2/28/2021 | $33,303 | $308,791 | $47,648,047 |
3/31/2021 | $39,005 | $347,796 | $52,864,986 |
4/30/2021 | $0 | $347,796 | $21,563,350 |
5/31/2021 | $0 | $21,911,146 | |
6/30/2021 | $0 | $21,911,146 | |
7/31/2021 | $0 | $21,911,146 | |
8/31/2021 | $0 | $24,345,717 |
Type in my dataset is the item type for each of of those values and dates, meaning, I need the running total calculated for each item based on the dates; as I change my filter from item 1 to item 2, etc, the new running total that comes up will be for that new item selected.
I have been trting different formulas based on Community posts regarding running totals for columns, but nothing has worked so far and I believe I have exhausted my search.
Thank you!
Solved! Go to Solution.
Turns out my logic was correct, I just needed to change the setting on my column to "Do not Summarize". Worked like a charm!
Turns out my logic was correct, I just needed to change the setting on my column to "Do not Summarize". Worked like a charm!
Hi @tharris ,
Glad to hear the issue is solved. You can accept your reply as solution, that way, other community members could easily find the answer when they get same issues.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |