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.
Hey,
I have the following input data and need to add the previous row "Value" to the last calculated value and keep getting the summation. This needs to be done grouped by the column id. The final result should be a measure which allways shows me the average sum for the Name=C starting from Rank 1.
Inputdata:
ID | Name | Value | Rank |
1 | A | 0 | 1 |
1 | B | 10 | 2 |
1 | C | 20 | 3 |
2 | A | 0 | 1 |
2 | B | 15 | 2 |
2 | B | 10 | 3 |
2 | C | 30 | 4 |
2 | D | 10 | 5 |
Table with summed Value (not a physical table, just to show what I mean)
ID | Name | Value | Value Sum | Rank |
1 | A | 0 | 0 | 1 |
1 | B | 10 | 0 | 2 |
1 | C | 20 | 10 | 3 |
2 | A | 0 | 0 | 1 |
2 | B | 15 | 0 | 2 |
2 | B | 10 | 15 | 3 |
2 | C | 30 | 25 | 4 |
2 | D | 10 | 55 | 5 |
Result:
Average Sum for C: 17,5
I hope it this clear what I´m trying to do. Thank you for your help!
regards
lucmax
Solved! Go to Solution.
Hi @lucmax
Not completely clear. You could
1. Create a calculated column in your table:
Value Sum =
CALCULATE (
SUM ( Table1[Value] ),
Table1[Rank] <= EARLIER ( Table1[Rank] ) - 1,
ALLEXCEPT ( Table1, Table1[ID] )
)
2. Create a measure for the average:
Average val = AVERAGE(Table1[Value Sum])
3. Set Table1[Name] in a slicer and select C, so that the measure shows the average for C
If you don't want to use the slicer, you could also hardcode that you want C in the measure, but i don't think that's very useful
Average val = CALCULATE( AVERAGE(Table1[Value Sum]), Table1[Name] = "C")
You could also do all this within a single measure (without the calculated column in the table) but, again, I don't think is the best way to go
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
with a measure and using the slicer (or other) to select the name:
Measure =
VAR auxT_ =
ADDCOLUMNS (
Table1,
"ValueSum_",
CALCULATE (
SUM ( Table1[Value] ),
Table1[Rank] <= EARLIER ( Table1[Rank] ) - 1,
ALLEXCEPT ( Table1, Table1[ID] )
)
)
RETURN
AVERAGEX ( auxT_, [ValueSum_] )
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Again, thank you! Works like a charm.
Hi @AlB ,
Thank you very much!
Just another question, how would I achive step 1, the calculated column, using a measure? I want to try and work without step 2 and 3 and use the measure directly in a table/matrix.
Hi @lucmax
Not completely clear. You could
1. Create a calculated column in your table:
Value Sum =
CALCULATE (
SUM ( Table1[Value] ),
Table1[Rank] <= EARLIER ( Table1[Rank] ) - 1,
ALLEXCEPT ( Table1, Table1[ID] )
)
2. Create a measure for the average:
Average val = AVERAGE(Table1[Value Sum])
3. Set Table1[Name] in a slicer and select C, so that the measure shows the average for C
If you don't want to use the slicer, you could also hardcode that you want C in the measure, but i don't think that's very useful
Average val = CALCULATE( AVERAGE(Table1[Value Sum]), Table1[Name] = "C")
You could also do all this within a single measure (without the calculated column in the table) but, again, I don't think is the best way to go
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
How can I do this same thing but instead of adding the next I just want to divide the by the previus number?
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 |