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
Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

Hi,

Share the download link of the PBI file with your measures already written.  Show the problem and the expected result.

The8's avatar
The8
Helper II
2 years ago

Hello Ashish_Mathur ,

FYI, In the loop of this topic, you can see I already had conversation with other community members but I coudn't able to solve the problem.

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 !!
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    That does not give me what i requested for in my previuos message.

    • The8's avatar
      The8
      Helper II
      2 years ago

      Here are details you requested.

      Hello Ashish_Mathur  ,

      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:

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

      3.
      "Net Price Total Date Material       =
          VAR __Table = SUMMARIZE( 'Sheet1', [Date],[Material],"__Value", [Net Price])
          VAR __Result = IF(HASONEVALUE(Sheet1[Date]), [Net price], SUMX(__Table, [__Value]))
      RETURN
          __Result
      "

      Below is the link to acces the file:
      https://drive.google.com/file/d/1tanNNwqShDfjB4L7AID--7MuPl52GBny/view?usp=drive_link

      I am expecting right totals as highlighted below.


       
      Thanks !




      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        2 years ago

        Access Denied message.