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

     

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?