Total meausre problems

[sdb_utd]

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%

[ImkeF · Accepted solution]

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])

)

[sdb_utd]

OMG, you would not believe how frustrating this was. Thanks!

[ImkeF]

I totally believe you!

Have been there so many times as well...

[sdb_utd]

Last one - I'm trying to get the total working for the start and end cost.

In the table, the values shown are the average start costs for each product. For the total I want the total of the start price, eg. in this case the total start cost should be 305 (sugar on 1st Feb and tea on 2nd Feb)

I don't seem to be able to get the syntax right.

StartCost = 
VAR datestart = CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[Product],'Table'[Date]))
return  
if (HASONEFILTER('Table'[Product]),
CALCULATE(average('Table'[Cost]),FILTER(ALL('Table'),'Table'[Date]=datestart)),
sumx(VALUES('Table'[Cost]),'Table'[Cost]))

[ImkeF]

Just to be sure (as I find that request a bit strange)

What do you want for Tea?:

1) What shall go into the total: 50 + 55 = 105

2) or actually what you have now: (50 + 55) / 2 = 52.5

And if it is 2: Are you sure that you don't want 107.5?