Sum various columns based on a condition set upon another group of columns

[Anonymous]

Hi all,

I am relatively new to Power BI and am having issues working out how to filter and sum a set of columns based on the value in one slicer. It is rather difficult to explain the issue, therefore I would like to define my problem by giving the below example.

Lets say I have the following sets of data:

Event data

Flight	Delay Reason 1	Delay Reason 2	Delay Reason 3	Delay Amount 1	Delay Amount 2	Delay Amount 3
0001	3	8	5	12	7	2
0002	3			63
0003	1	9		9	3
More Data

Delay Categories

Category	Code
A	1
A	2
B	3
B	4
B	5
B	6
B	7
C	8
C	9
C	10

Now I would like the user to be able to filter one or more Categories and then calculated the total delay (at flight level) for all related delays and subsequently determine the number of flights that had less than 20 minutes delay.

For example if the user selected Category B (Codes: 3,4,5,6,7) then the data returned would be:

Event data (Filtered for Category B)

Flight	Delay Reason 1	Delay Reason 2	Delay Reason 3	Delay Amount 1	Delay Amount 2	Delay Amount 3	Delay Total
0001	3	8	5	12	7	2	14
0002	3			63			63
0003	Deleted
More Data

The total number of flights with a delay greater than 20 minutes would only be one as the delay minutes attributed to Delay Reason 2 are not included.

I have tried to create a relationships between Delay Categories (Table) and Delay Reason 1, Delay Reason 2 and Delay Reason 3. However Power BI will only allow me to keep one active at all times.

Please note that pivotting the table to show all delay reasons in 1 column did not work as I only wanted a flight to count once (Flight 0001 in the above example).

Please let me know if there is a simple solution.

[v-chuncz-msft]

@Anonymous,

You may use SELECTCOLUMNS Function (DAX) and UNION Function to add a calculated table.

[Greg_Deckler]

First, I would suggest unpivoting your Delay Reason columns in the Query Editor. Next question, are your Codes (1, 2, 3, 4...) really mismatched from your Column headings (Delay Reason 1, Delay Reason 2,...)? Should not be an issue either way but changes the solution.

[Anonymous]

Hi Greg,

Thanks for your reply. The data itself comes like that in its raw form, but I’m more than happy to unpivot it. However I’ll need to find a way to sum up the delay total at a flight level basis once done.

Regarding the relationship between the categories... they are exactly as shown in the table. In the raw data set the delay codes willl be associated to a delay category, I.e codes 1-3 is related to weather, 4-9 ground handling, etc

Hope that helps?