Table Total Incorrect with Measure A using in New Measure

[The8]

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 in € = DIVIDE(SUM(Sheet1[Price]),SUM('Sheet2'[From € to $ (Conv. Rate)]),0)

Net price = DIVIDE([Final Price in €],SUM(Sheet1[Quantity]),0)

But when I use this measure I have incorrect total values for measure "Net price" as below (I have checked the formats of each column to be in decimal format)

Any suggestions to solve this ?!

Thanks in advance

@Ashish_Mathur   (May be can you help me regarding this post!)

[Ashish_Mathur]

Hi,

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

[The8]

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:

Material	Date	Quantity	Price	Currency
X1	01.01.2024	30	86	EUR
X2	01.01.2024	20	177	USD
X3	01.01.2024	15	36	EUR
X1	01.02.2024	43	81	EUR
X2	01.02.2024	46	165	USD
X3	01.02.2024	34	40	EUR
X1	01.03.2024	28	85	EUR
X2	01.03.2024	46	175	USD
X3	01.03.2024	32	35	EUR

Sheet 2 :

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

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]

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

[The8]

Here are details you requested.

Hello @Ashish_Mathur  ,

Sheet 1:

Material	Date	Quantity	Price	Currency
X1	01.01.2024	30	86	EUR
X2	01.01.2024	20	177	USD
X3	01.01.2024	15	36	EUR
X1	01.02.2024	43	81	EUR
X2	01.02.2024	46	165	USD
X3	01.02.2024	34	40	EUR
X1	01.03.2024	28	85	EUR
X2	01.03.2024	46	175	USD
X3	01.03.2024	32	35	EUR

Sheet 2 :

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

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 !

[The8]

Hi @Ashish_Mathur ,

I have updated the link.

https://drive.google.com/file/d/1tanNNwqShDfjB4L7AID--7MuPl52GBny/view?usp=sharing

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[Ashish_Mathur]

Access Denied message.

[Greg_Deckler]

@The8 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[The8]

@Greg_Deckler I have already gone through your post and videos but I coudn't able to solve the problem. I coudn't able to get your solution methodology exactly in my scenario.

That would be great if have a solution exactly according to my data. 

Thanks !!

[Greg_Deckler]

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

[The8]

Hello @Greg_Deckler ,

Sheet 1

Material	Date	Quantity	Price	Currency
X1	01.01.2024	30	86	EUR
X2	01.01.2024	20	177	USD
X3	01.01.2024	15	36	EUR
X1	01.02.2024	43	81	EUR
X2	01.02.2024	46	165	USD
X3	01.02.2024	34	40	EUR
X1	01.03.2024	28	85	EUR
X2	01.03.2024	46	175	USD
X3	01.03.2024	32	35	EUR

Sheet 2

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

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 !!

[Greg_Deckler]

@The8 See PBIX attached below signature.

[The8]

Hello @Greg_Deckler 

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

Thanks !!

[Greg_Deckler]

@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

[The8]

Hi @Greg_Deckler 

I have gone through your posts and I have also tried DAX with material but I got error even though.

Modified Measure I used is 

"
Net Price Total =

    VAR __Table = SUMMARIZE( 'Sheet1', [Date],[Material], "__Value", [Net Price])

    VAR __Result = IF(HASONEVALUE(Sheet1[Date]), [Net price], SUMX(__Table, [__Value]))

RETURN

    __Result
"

Can you help me regarding this?

Thanks in advance @Greg_Deckler 

[Greg_Deckler]

@The8 Actually the relationship direction needed to be both and not single direction and that was throwing the numbers off. See updated file.

[The8]

Hi @Greg_Deckler 

Thank you, Good day!

You are exactly right, when we change relationship direction to "both" and adding Material into my measure as below is working absolutely fine even with Material Slicer.

"
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"

But when I make slicer selection based on date again I have same problem, totals being wrong as below image for your reference.

With Date SLicer Selection:


Any changes needs to be done still ?

[Greg_Deckler]

@The8 That would be:

Net Price Total Date Material =

    VAR __Table = SUMMARIZE( ALLSELECTED('Sheet1'), [Date],[Material],"__Value", [Net Price])

    VAR __Result = IF(HASONEVALUE(Sheet1[Date]), [Net price], SUMX(__Table, [__Value]))

RETURN

    __Result"

[The8]

Hi @Greg_Deckler 

Thanks for your update.

But I still have the save problem with incorrect totals.

Measure I used 

Net Price Total Date Material =

    "VAR __Table = SUMMARIZE( ALLSELECTED('Sheet1'), [Date],[Material],"__Value", [Net Price])

    VAR __Result = IF(HASONEVALUE(Sheet1[Date]), [Net price], SUMX(__Table, [__Value]))

RETURN

    __Result"