Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I need to sum a column conditionally based on another column then divide that sum by another single value from another column. Looking at the data in the screen clip, I need to sum the numbers in column B when column D is less than C and then divide by column A all per column E. So, in this case, the numbers would look like this. (225 + 5) /230 = 1. If row 67 did not meet the case of the date in D being less than C then I would expect the answer would be 225 / 300 = 0.978.
First you need to create an index column in order to compare rows. Then you can create a custom column (in Edit Query) with something like the following formula:
if #"Added Index"{[Index]+1}[E] = [E] then if [D] < [C] then (#"Added Index"{[Index]+1}[B] + [B]) / [A] else [B] / [A] else ??
You need to first add an index to your table so you can compare rows.
Then you could create a new custom column with a formula something like this:
if #"Added Index"{[Index]+1}[E] = [E] then if [D] < [C] then (#"Added Index"{[Index]+1}[B] + [B]) / [A] else [B] / [A] else ??
Thanks but I think you need more information. The uniqueness per row is based on column E and the column to the left of C. Also there is not always just 2 rows per column E. The number of rows per column E could be upto 10.
I added an index column named ID and I am trying to write the code for the "Custom Column" but it errors out.
Error: The name 'ID" wasn't recognized.
= if #"ID"{[Index]+1}[Submittal Number] = [Submittal Number] and #"ID"{[Index]+1}[Submittal Pass] = [Submittal Pass] then
if [Actual Submission Date] <= [Due Date] then
(#"ID"{[Index]+1}[#"Deliverables ""Accept"] + [#"Deliverables ""Accept"]) / [Deliverables]
else
0
else [#"Deliverables ""Accept"] / [Deliverables]
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |