topic Re: Avoid sum multiple times same value on column in DAX Commands and Tips

Avoid sum multiple times same value on column

bvramilo — Tue, 09 Apr 2024 09:42:14 GMT

Hi everyone,

I have being problems calculating a total on a Matrix:

I have this structure:

Batch

IdBatch

Amount

Connection 1-N with Component table

Component

IdComponent

IdBatch

Connected 1-N with Failure table

Failure

IdComponent

AmoutFailures

Date

So, with this information I create a view like this:

IdBatch	Date	Amount	AmountFailures
1	01/04/2024	100	2
1	02/04/2024	100	5
2	01/04/2024	300	2
3	10/04/2024	500	0
1	10/04/2024	100	10

But when I want to see the relation between number of failures and batch amout, I can calculate de total of failures in the right way, but I am sum more batch amout that I should do

The right result should be like this:

Batch	Year	Amount	AmoutFailures
1	2024	100	17
2	2024	300	2
3	2024	500	0

But I am getting this result:

Batch	Year	Amount	AmoutFailures
1	2024	300	17
2	2024	300	2
3	2024	500	0

As you can see, I am summing the batch amout 3 times.

Maybe, the way I am creating the view is wrong but I don't know how I should create it to import and work easily in Power BI.

Thank you in advance

Re: Avoid sum multiple times same value on column

sjoerdvn — Tue, 09 Apr 2024 12:40:44 GMT

I guess with "view" you mean a table or matrix visual, right? assuming that you could just use the amount column from the Batch table and make sure it is not using it as an implicit measure, so:

Re: Avoid sum multiple times same value on column

Rupak_bi — Tue, 09 Apr 2024 12:51:55 GMT

Hi,

You need to put the amount column in the row and failures in the value of a matrix visual. you may use date as slicer or in the row above amount in the matrix visual as well.

Re: Avoid sum multiple times same value on column

bvramilo — Wed, 10 Apr 2024 08:57:19 GMT

Thank you so much for the quickly answers, they made me think and prepare and simple example that helps to find the solution:

When I am talking about a view, I mean a database view (data from multiple tables together) and that's here where I made a mistake

My model was like that:

That's the data

So, when I go level down in the grid the behaviour was like this:

As you can see, power bi is multipling the batch amount by the number of registers so the total amout is wrong.

But, as I said before, to show you a simple model I create this one:

It works well cause Power BI knows the amout of batch is not a part of the registers.

Now I have to find how to make it work without both direction relation between Revision and Batch. I read that is not recommended

Sorry for make you waste your time and thank you so much.

Regards,

Re: Avoid sum multiple times same value on column

Sahir_Maharaj — Fri, 12 Apr 2024 20:21:55 GMT

Hello bvramilo,

Can you please try the following:

Total Batch Amount = SUMX( VALUES(Batch[IdBatch]), CALCULATE( MAX(Batch[Amount]) ) )