Forum Discussion

sdb_utd's avatar
sdb_utd
Frequent Visitor
6 years ago
Solved

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

ProductCostDate
Sugar5001/02/2020
Sugar5501/02/2020
Sugar15003/02/2020
Sugar4004/02/2020
Sugar4504/02/2020
Tea10002/02/2020
Tea11002/02/2020
Tea50003/02/2020
Tea9008/02/2020
Tea9508/02/2020

 

Report

ProductStartCostEndCostChangeCostTotal Cost all productsPercent of totalWeighted Change
Sugar52.542.5-19.05%340123527.53%-5.24%
Tea10592.5-11.90%895123572.47%-8.63%
Total52.5092.576.19%12351235100.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])
    )

     

5 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion

    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's avatar
      sdb_utd
      Frequent Visitor

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

      • ImkeF's avatar
        ImkeF
        Community Champion

        I totally believe you!

        Have been there so many times as well...