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 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]
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |