Calculation based on other colum with multiple values separated by _

[Sander_NL]

I would like to have a new column which, based on the new count column (= measure _MNewCount), calculates the rate calculated on the basis of the colour column in the Tariff table.

The source csv file delivers a  column with color_color_color convention with a different numbers of colors so can be 1 to several seperated by "_".

Some products have no color option. Sometimes there are also colours that do not appear in the rates table which may then be ignored. 

What is de best approach?

 Thanks in advance for your help!

Concept PBIX file https://www.dropbox.com/s/zjrvh66165b7khd/Color.pbix?dl=0

[Sander_NL]

I would like to have a new column which, based on the new count column (= measure _MNewCount), calculates the rate calculated on the basis of the colour column in the Tariff table.

The source csv file delivers a  column with color_color_color convention with a different numbers of colors so can be 1 to several seperated by "_".

Some products have no color option. Sometimes there are also colours that do not appear in the rates table which may then be ignored. 

What is de best approach?

Thanks in advance for youor help!

Concept PBIX file:

https://www.dropbox.com/s/zjrvh66165b7khd/Color.pbix?dl=0

[Ashish_Mathur]

Hi,

What exact numerical answer are you expecting in a new column of your visual?

[Sander_NL]

Hi thanks for your response Ashish:

Product	Option	Orginal_count	New Count	Outcome
Test3	Green_Grey_White	4	12	5400
Test5	Red_Blue	3	9	2700
Test4		2	6	0
Test2	Red	2	6	600
Test1	Red_Blue_Grey	1	3	1350

[Ashish_Mathur]

Hi,

You may refer to my solution here.

Hope this helps.

[Sander_NL]

This is the result I need. One small question:

The total is 9.750 but when I calculated this manually it should be: 10.050.

[Ashish_Mathur · Accepted solution]

Hi,

Try this formula

=if(HASONEVALUE('Product'[Product]),SUM('Product'[tariff])*[_MNewCount],SUMX(SUMMARIZE('Product','Product'[Product],"ABCD",SUM('Product'[tariff])*[_MNewCount]),[ABCD]))

Hope this helps.