topic Re: Table Total Incorrect with Measure A using in New Measure in Desktop

Table Total Incorrect with Measure A using in New Measure

The8 — Thu, 02 May 2024 09:42:39 GMT

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

Re: Table Total Incorrect with Measure A using in New Measure

Greg_Deckler — Tue, 09 Apr 2024 12:45:50 GMT

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

Re: Table Total Incorrect with Measure A using in New Measure

The8 — Wed, 10 Apr 2024 11:55:03 GMT

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

Re: Table Total Incorrect with Measure A using in New Measure

Greg_Deckler — Wed, 10 Apr 2024 13:08:25 GMT

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

Re: Table Total Incorrect with Measure A using in New Measure

The8 — Wed, 10 Apr 2024 19:24:56 GMT

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

Re: Table Total Incorrect with Measure A using in New Measure

Greg_Deckler — Wed, 10 Apr 2024 15:30:41 GMT

The8 See PBIX attached below signature.

Re: Table Total Incorrect with Measure A using in New Measure

The8 — Sun, 14 Apr 2024 10:42:22 GMT

Hello Greg_Deckler

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

Thanks !!

Re: Table Total Incorrect with Measure A using in New Measure

Greg_Deckler — Fri, 12 Apr 2024 13:47:35 GMT

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

Re: Table Total Incorrect with Measure A using in New Measure

The8 — Sun, 14 Apr 2024 16:16:23 GMT

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

Re: Table Total Incorrect with Measure A using in New Measure

Greg_Deckler — Sun, 14 Apr 2024 18:15:46 GMT

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

Re: Table Total Incorrect with Measure A using in New Measure

The8 — Mon, 15 Apr 2024 07:13:03 GMT

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 ?

Re: Table Total Incorrect with Measure A using in New Measure

Greg_Deckler — Mon, 15 Apr 2024 13:08:33 GMT

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"

Re: Table Total Incorrect with Measure A using in New Measure

The8 — Tue, 16 Apr 2024 10:48:12 GMT

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"

Re: Table Total Incorrect with Measure A using in New Measure

Ashish_Mathur — Thu, 02 May 2024 09:51:26 GMT

Hi,

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

Re: Table Total Incorrect with Measure A using in New Measure

The8 — Thu, 02 May 2024 10:37:06 GMT

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

Re: Table Total Incorrect with Measure A using in New Measure

Ashish_Mathur — Thu, 02 May 2024 10:44:21 GMT

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

Re: Table Total Incorrect with Measure A using in New Measure

The8 — Thu, 02 May 2024 11:24:08 GMT

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 !

Re: Table Total Incorrect with Measure A using in New Measure

Ashish_Mathur — Thu, 02 May 2024 11:38:24 GMT

Access Denied message.

Re: Table Total Incorrect with Measure A using in New Measure

The8 — Thu, 02 May 2024 12:04:43 GMT

Hi Ashish_Mathur ,

I have updated the link.

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

Re: Table Total Incorrect with Measure A using in New Measure

Ashish_Mathur — Thu, 02 May 2024 23:56:36 GMT

Hi,

PBI file attached.

Hope this helps.