Forum Discussion

The8's avatar
The8
Helper II
2 years ago
Solved

Table Total Incorrect with Measure A using in New Measure

Hi, I have loaded data into power BI from excel and I have created relationship betweeen tables using currency column. Sheet 1 Sheet 2 Now I have following measures Final Price...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

The8 Cool, please post your data as text and all relevant formulas.

The8's avatar
The8
Helper II
2 years ago

Hello Greg_Deckler ,

Sheet 1

MaterialDateQuantityPriceCurrency
X101.01.20243086EUR
X201.01.202420177USD
X301.01.20241536EUR
X101.02.20244381EUR
X201.02.202446165USD
X301.02.20243440EUR
X101.03.20242885EUR
X201.03.202446175USD
X301.03.20243235EUR


Sheet 2

From € to $ (Conv. Rate)Currency
1,08USD
1EUR



Both this tables are in relationship by column Currency.

Measures:

Final Price in € = DIVIDE(SUM(Sheet1[Price]),SUM('Sheet2'[From € to $ (Conv. Rate)]),0)
Net price = DIVIDE([Final Price in €],SUM(Sheet1[Quantity]),0)

Thanks !!







    • The8's avatar
      The8
      Helper II
      2 years ago

      Hello Greg_Deckler 

       

      Is that possible even when I take material into a table or matrix in my case?

      Thanks !!

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        2 years ago

        The8 I don't think that you are taking the time to read up on this issue, particular the second link I posted that goes through the issue in detail and how to fix it. If you include additional columns in your table visual, you have to add those columns to your SUMMARIZE like this:

        Final Price Total = 
    VAR __Table = SUMMARIZE( 'Sheet1', [Date],[Material], "__Value", [Final Price in €])
    VAR __Result = IF(HASONEVALUE(Sheet1[Date]), [Final Price in €], SUMX(__Table, [__Value]))
RETURN
    __Result