Create Summary Table from Column Data

[Mike_RPMed]

Hey All,

I am trying to create a pareto chart in PowerBI using some logged manufacturing data. Here is an example table of the entry log:

Lot	Code	Failure Mode 1	Failure Mode 2	Failure Mode 3	Failure Mode 4	Failure Mode 5
22082315V	A1	55	151	21	0	54
22083010V	A2	36	26	7	2	14
22090102V	A3	0	48	0	0	12
22082915V	A1	0	102	0	0	38
22083001V	A3	0	40	5	0	22
22090101V	A4	0	76	14	0	26
22090707V	A2	18	7	7	0	4
22091209V	A4	0	134	36	0	63
22090809V	A1	0	53	0	0	33
22091208V	A1	21	113	11	0	59
22090810V	A3	18	60	37	0	24
22091310V	A2	0	104	14	0	57
22091915V	A4	27	148	28	0	64
22091903V	A2	9	64	12	0	26
22091412V	A1	0	31	23	2	22

I want to create a table in PowerBI that will make a row for each of the failure modes, and summarize the total in each category based upon whatever filter is active on the page (many other parameters are linked by lot or code). 

I imagine the output table being something like this with the total failures changing based upon the active filters, so that I can create an interactive pareto chart:

Failure Mode	Total Failures
Failure Mode 1	184
Failure Mode 2	1157
Failure Mode 3	215
Failure Mode 4	4
Failure Mode 5	518

I've tried a bunch of things but can't seem to figure it out. The closest I think I've gotten is to try and make a measure for each failure mode, but I can't figure out how to put that into a table that I can create a pareto chart from.

Any help would be greatly appreciated, thanks in advance.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

I suggest having a unpivotted table like below.

Unpivotting operation can be done in Power Query Editor.

Failure pareto percent: = 
VAR _failure = [Total failure:]
VAR _allfailure =
    CALCULATE ( [Total failure:], ALLSELECTED ( Data[Failure Mode] ) )
RETURN
    DIVIDE (
        CALCULATE (
            [Total failure:],
            FILTER ( ALLSELECTED ( Data[Failure Mode] ), [Total failure:] >= _failure )
        ),
        _allfailure
    )