Sum across multiple columns based on values in *different* columns

[Daryl_K]

Say you have a table like this:

Issue  |  Causal 1  |  Causal 1 Quantity  |  Causal 2  |  Causal 2 Quantity...

For each row I want to sum all of the columns that end in "Quantity" based on what the corresponding Causal number says.

I expect to have a separate IF statement for each Causal, but if Causals 1, 3, 4 and 7 (only) are "loose" then I want to sum the values in Causal Quantity 1, 3, 4 and 7 only. I am thinking then a sepearate line would examine all the Causals for "Missing" (or whatever) and do a sum on those corresponding Quantities.

I did something like this sucessfully once before but the filter and quantity values were not two differnet columns, so I am having difficulties on this one.

TIA.

[v-piga-msft]

Hi @Daryl_K ,

I still have a little confused about your scenario.

I expect to have a separate IF statement for each Causal, but if Causals 1, 3, 4 and 7 (only) are "loose" then I want to sum the values in Causal Quantity 1, 3, 4 and 7 only. I am thinking then a sepearate line would examine all the Causals for "Missing" (or whatever) and do a sum on those corresponding Quantities.

---

What does mean of "loose"?

If it is convenient, could you share your data sample and your desired output so that I could understand your logic better and get the solution quickly.

Best Regards,

Cherry

[Daryl_K]

I only used "Loose" as an example Causal. It means nothing. We could use anything in it's place.

Example 2:

Issue     |     Causal 1   |   Causal 1 Quantity  |   Causal 2   | Causal 2 Quantity   |   Causal 3   |    Causal 3 Quantity

Fruit            Berries                      3                     Apples                  5                     Berries                      2

Seeds          Guava                       7                     Guava                   4                     Apples                      3

So in this over-simplified example, Issue 1 ("Fruit") would sum as: Berries = 5, Apples = 5, while Issue 2 ("Seeds") would be Guava = 11, Apples = 3.

Does this make more sense?

[v-piga-msft]

Hi @Daryl_K ,

Actually, for your scenario, the easiest way for us is to change the table structure like below in Query Editor.

We could get the output below with Append Queries in Query Editor.

Then you could create the matrix in Power BI like below.

The details steps in Query Editor, you could refer to my attachement.

Best Regards,

Cherry

[Daryl_K]

Hi.

A coworker gave me a solution that ended up working quite well.

She suggested using a combination of UNION and SELECTCOLUMNS to create a new table from the desired columns, which can then be "sliced and diced" via my slicers.

It works perfectly! If anyone needs specific examples let me know.

---

[Daryl_K]

Well, I took a crack at formulating something, and tried this:

Wrong Part Quantity SUM = 
    CALCULATE('MIR-OCN List', 'MIR-OCN List'[Issue1Quantity] + 'MIR-OCN List'[Issue2Quantity] + 'MIR-OCN List'[Issue3Quantity] + 'MIR-OCN List'[Issue4Quantity] 
        + 'MIR-OCN List'[Issue5Quantity] + 'MIR-OCN List'[Issue6Quantity] + 'MIR-OCN List'[Issue7Quantity] + 'MIR-OCN List'[Issue8Quantity],
		FILTER(
			'MIR-OCN List',
            [Issue1Causal] = "Wrong Part Quantity" || [Issue2Causal] = "Wrong Part Quantity" || [Issue3Causal] = "Wrong Part Quantity" || [Issue4Causal] = "Wrong Part Quantity"
				|| [Issue5Causal] = "Wrong Part Quantity" || [Issue6Causal] = "Wrong Part Quantity" || [Issue7Causal] = "Wrong Part Quantity" || [Issue8Causal] = "Wrong Part Quantity"
		))

But alas it gives me the error that it contains multiple columns where only a aingle column can be used in a true/false expression for a filter.

So it's an invalid use, but maybe the actual expression I tried clarifies what I am looking for a little better?