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...
Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

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

     

ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
6 years ago

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
    6 years ago

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

    • ImkeF's avatar
      ImkeF
      Icon for Community Champion rankCommunity Champion
      6 years ago

      I totally believe you!

      Have been there so many times as well...

       

      • sdb_utd's avatar
        sdb_utd
        Frequent Visitor
        6 years ago

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