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 am trying to create a weight for product attributes using: Price / Average Price of the Same Item Type in the Same Month. I have done the same calulations in excel using an AVERAGEIFS function in the denominator but cant seem to replicate the same results in Power BI.
My current column formula is:
Item # | Item Type | Month-Year | Price | Weight |
8001 | 1 | January 2019 | 2150 | 0.2973 |
8002 | 2 | January 2019 | 10304 | 1.4248 |
8003 | 1 | January 2019 | 7811 | 1.0801 |
8004 | 1 | January 2019 | 6540 | 0.9043 |
8005 | 2 | January 2019 | 15007 | 2.0751 |
8006 | 1 | January 2019 | 4991 | 0.6901 |
8007 | 1 | January 2019 | 5989 | 0.8281 |
8008 | 1 | February 2019 | 9028 | 1.2484 |
8009 | 2 | February 2019 | 2784 | 0.3850 |
8010 | 2 | February 2019 | 4744 | 0.6560 |
8011 | 1 | February 2019 | 1078 | 0.1491 |
8012 | 2 | February 2019 | 13386 | 1.8510 |
8013 | 1 | February 2019 | 9877 | 1.3658 |
8014 | 2 | March 2019 | 3764 | 0.5205 |
8015 | 2 | March 2019 | 11024 | 1.5244 |
Excel Results
A | B | C | D | E |
Item # | Item Type | Month-Year | Price | Weight |
8001 | 1 | Jan-19 | 2150 | 0.3912 |
8002 | 2 | Jan-19 | 10304 | 0.8142 |
8003 | 1 | Jan-19 | 7811 | 1.4212 |
8004 | 1 | Jan-19 | 6540 | 1.1899 |
8005 | 2 | Jan-19 | 15007 | 1.1858 |
8006 | 1 | Jan-19 | 4991 | 0.9081 |
8007 | 1 | Jan-19 | 5989 | 1.0897 |
8008 | 1 | Feb-19 | 9028 | 1.3554 |
8009 | 2 | Feb-19 | 2784 | 0.3993 |
8010 | 2 | Feb-19 | 4744 | 0.6805 |
8011 | 1 | Feb-19 | 1078 | 0.1618 |
8012 | 2 | Feb-19 | 13386 | 1.9201 |
8013 | 1 | Feb-19 | 9877 | 1.4828 |
8014 | 2 | Mar-19 | 3764 | 0.5091 |
8015 | 2 | Mar-19 | 11024 | 1.4909 |
where the weight function is: = D2 / AVERAGEIFS($D:$D,$B:$B,B2,$C:$C,C2)
Any help would be greatly appreciated, thanks!
Solved! Go to Solution.
Please test this as I looked at relatively briefly.
The calculated column should be:
ChilliWeight = 'Sales'[Price] / CALCULATE(AVERAGE('Sales'[Price]), FILTER ( 'Sales', 'Sales'[Month-Year] = EARLIER('Sales'[Month-Year]) && 'Sales'[Item Type] = EARLIER('Sales'[Item Type] )))
The reason the original code didn't work is that the denominator was 7231.8 for every row
Please test this as I looked at relatively briefly.
The calculated column should be:
ChilliWeight = 'Sales'[Price] / CALCULATE(AVERAGE('Sales'[Price]), FILTER ( 'Sales', 'Sales'[Month-Year] = EARLIER('Sales'[Month-Year]) && 'Sales'[Item Type] = EARLIER('Sales'[Item Type] )))
The reason the original code didn't work is that the denominator was 7231.8 for every row
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |