Avoid sum multiple times same value on column

[bvramilo]

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

[Sahir_Maharaj]

Hello @bvramilo,

Can you please try the following:

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

[bvramilo]

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, 

[Rupak_bi]

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.

[sjoerdvn]

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: