Forum Discussion
Calculation based on other colum with multiple values separated by _
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
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.
6 Replies
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:
Hi,
What exact numerical answer are you expecting in a new column of your visual?
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
