Forum Discussion
Total meausre problems
Sample PBIX - https://www.dropbox.com/s/vob4ngqfkxt3ss5/TeaSugar_Sample.pbix?dl=0
I've read several posts on this, but it's still not working for me. I'm looking at an invoice table with products, costs and dates. I want to take the average price for a product at the earliest date and compare it with the average price for a product on the lastest date.
The start price appears OK with this:
StartCost =
VAR datestart = CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[Product],'Table'[Date]))
return CALCULATE(average('Table'[Cost]),FILTER(ALL('Table'),'Table'[Date]=datestart))
Same for the latest cost, but obviously with MAX in the variable.
The change is OK, showing an increase or decrease on the earliest cost. Percent of total is also OK.
Using the percent of total as a weight, the weighted change figure measure is:
Weighted Change =
if (HASONEFILTER(('Table'[Product])),
[Change]*[Percent of total],
SUMX('Table',[Change]*[Percent of total])
)
And this is where it falls down, just showing 0, rather than -13.87%
Any ideas?
Ideally I would like to turn off the table totals and show the total weighted change on a card separate to the table.
Source Data
| Product | Cost | Date |
| Sugar | 50 | 01/02/2020 |
| Sugar | 55 | 01/02/2020 |
| Sugar | 150 | 03/02/2020 |
| Sugar | 40 | 04/02/2020 |
| Sugar | 45 | 04/02/2020 |
| Tea | 100 | 02/02/2020 |
| Tea | 110 | 02/02/2020 |
| Tea | 500 | 03/02/2020 |
| Tea | 90 | 08/02/2020 |
| Tea | 95 | 08/02/2020 |
Report
| Product | StartCost | EndCost | Change | Cost | Total Cost all products | Percent of total | Weighted Change |
| Sugar | 52.5 | 42.5 | -19.05% | 340 | 1235 | 27.53% | -5.24% |
| Tea | 105 | 92.5 | -11.90% | 895 | 1235 | 72.47% | -8.63% |
| Total | 52.50 | 92.5 | 76.19% | 1235 | 1235 | 100.00% | 0.00% |
Hi sdb_utd ,
you have to iterate over the VALUES of the Product-column like so:
Weighted Change =if (HASONEFILTER(('Table'[Product])),[Change]*[Percent of total],SUMX(VALUES('Table'[Product]),[Change]*[Percent of total]))