Dear community,
I am working on a dashboard and I am struggling to make a certain calculation. I am not used to making a lot of calculations et cetera in Power BI but at my new job I have to, since I am not allowed to clean, transform and calculate data in KNIME before importing it into Power BI. In KNIME it is not hard at all to make the calculation I am trying to make now in Power BI, so I hope you can help me out.
I have three related columns:
- Quotes
- Quote_items
- Quote_classification
For each Nr_ in 'Quotes', one or more rows in 'Quote_items' form the total [Amount] of the order. That total quote amount can't be found in 'Quotes', unfortunately. So there is a one-to-many relationship between these tables.
Each Nr_ also contains a classification. 'Quote_classification' is a small table that contains a weightingfactor for each classification. This factor is used to calculate a simple expectation of future sales. 'Quote_classification' is related to 'Quotes' in a one-to-many manner.
I am trying to make a new column/measure/whatever that does the following calculation:
1. SUM(All subtotals) from 'Quote_items' for a given Nr_ in 'Quotes'
2. Multiply this total with the corresponding factor from 'Quote_classification'.
A few months back I would do these calculations via KNIME and just make a new column. Now I need to do this in Power BI. Can someone help me out?
Thanks in advance!
