How to Create a Column that Summarizes the Others

[SydneyAM]

Hello,

I've got a challenging problem on my hands. I have a bunch of auditing data that we would like to track overtime by why something failed. However, the way the data is formated is with a single pass/fail column for an over all result and then multiple columns of auditing variables that also say either pass or fail. 

I want to be able to categorize the failures by which variable failed. 

I want to be able to create a graph showing number of failed audits over time and those broken down into failure category. 

So how can I create a column that essentially summarizes all the variable columns. 

[Anonymous · Accepted solution]

Hi @SydneyAM ,

Based on the description, please try the following methods:

1.Create the sample table.

2.Open the power query editor, select add custom column. Enter the following formula.

if [Result] = "fail" then
         if [Ship to] = "fail" then "Ship to failed" else
         if [Qty] = "fail" then "Qty failed" else
         if [Selling Price] = "fail" then "Selling Price failed" else
         if [First Date] = "fail" then "First Date failed" else
         "Other reason"
       else
         "pass"

3.Select ok. The result is shown below.

4.Close and apply. Drag the fields to the column chart.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

As part of your data preparation you unpivot the data to transform it from a wide table (like you are used to in Excel) to a narrow table (like you should be using in Power BI).  Then the summarization is very easy.