SUM formula in several column with condition

[wenniin]

I have 6 columns:

                | p1          | p2          | p3          | p4          | p5          | cost       |  

               -------------------------------------------------------------------

ex1          | apple     | orange   | apple     | apple     | grape     | 500        |              

ex2          | apple     | orange   | grape     |               |               | 300        |

ex3          | apple     | orange   | orange   |               |               | 750        |

ex4          | apple     |               |                |               |               | 250        |

the result I expected: 

ex1= 1 apple, 1 orange, 1 grape

ex2= 1 apple, 1 orange, 1 grape

ex3= 1 apple, 1 orange

ex4= 1 apple

cost don't depend on anything.

I need to make a bar chart (for all 5 columns) to show how many each product appear, each product will be count as 1 in the same example no matter how much the product appears.

I already tried pivot and the result is exactly what I want BUT it multiply cost column and ruined everything.

is there any other way to do it? Sorry if this is a basic problem, I'm just a beginner in power BI. any suggestion will be appreciated

[MFelix]

Hi @wenniin,

If you want to make hte count of the products follow the steps:

1. Unpivot p columns
2. Remove blanks
3. Create a bar chart add the fields like this:
   1. ex - Axis
   2. fruit - legend
   3. fruit - values - summarize option distinct count

Result should be as you want check below:

Regards,

MFelix

[wenniin]

sorry for the late response, I actually have many column in my table but only taking a neccesary column for ask.

I need a 'year' column to make this:

Bar Chart

 I need bar chart to show me the quantity of product (apple, orange, grape) in 5 column (p1-p5) for certain condition (the product only count as 1 even if it shows more than once in the same row)

I'm using p1-p5 as axis (but it only calculating p1 and it is my current problem) and -count of 'year'- as value

Sorry for my lack of description and everything, please tell me if you have another suggestions. thank you

[Greg_Deckler]

Perhaps you just need to use AVERAGE for your cost instead of SUM and then you would be good?

[wenniin]

sorry if my question is confusing, I don't need the calculation of cost. The one I need is a total of product (p1-p5), but with a certain condition: if the product appear more than 1 in a row, it will only count as 1.

I can get the result I want with pivot function but it multiplied my cost column so I can't use that function