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]))
      • ImkeF's avatar
        ImkeF
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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?