Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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 |
Hi,
You may refer to my solution here.
Hope this helps.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |