DAX MEASURE for limited weight

[Andvil]

Good afternoon to everyone,

I am trying to do a report where I filter a maximum of weight according to a sales code. Sales codes have a variety of products and weights registered in the data base. I need to sum all the weights under each code and show the information that has a maximum weight of 250 kg.

I have two data bases and I am attaching an example so you can see what I am trying to do. In the Los Angeles database, I was able to filter all sales codes where the sum of product weights is less than 250 Kg. In the visual, you will see how the dax formula weight filtered matches the weight registered in te system. 

However, I am trying to do the same with the Canada data base: Filter the visual to show only the information where the sum of weights in a sales code is maximum 250 Kg, but in the visual, every row has the same weight (which is the total).

I am using exactly the same formula as in the Los Angeles database, and relationships between dates table and the sales database is the same, so I cannot figure out why I am having this error in one database and the other one is correct. I also need to filter weights if I select a month. For example, If i decide to show only sales from March, the visuals should filter sales from this month under 250 Kg.

I appreciate you help, If you need further information let me know.

I attach the link to download the sample data: SAMPLE SALES DATA 

This visual shows the incorrect row weights I receive.

Best,

Andvil

[v-yiruan-msft]

Hi @Andvil ，

Please update the formula of measure [DAX WEIGHT SF] under table DAU_EXPORT_2021 as below:

DAX WEIGHT SF = CALCULATE(SUM(DAU_EXPORT_2021[POSIBLE PESO NETO ITEM]))

Best Regards

[Andvil]

Hello @v-yiruan-msft ,

It worked, however, when I add a visual showing sold products and filter to have sales codes under 250 Kg, I have different values from the other visuals (but all visuals should have the same values).

I attach the link for the updated file and visual: Updated Example 

Thanks in advance for your help.

Andvil

[v-yiruan-msft · Accepted solution]

Hi @Andvil ,

As checked the third visual in the report page "Los Angeles" and "Canada", I found that their first hierarchy fields are different: Los Angeles page used the field [POSIBLE AGENCIA DE CARGA](Agent), however Canada page used the field [POSIBLE DESCRIPCION PRODUCTO COMERCIAL](Product). When Canada page also applied the field [POSIBLE AGENCIA DE CARGA] in the third visual, the total values in all of the table visuals can keep the same just as shown in below screenshot. So please check whether the table make the proper setting? And the total value in third visual is different with the one in other table visuals, whether it is due to include the duplicated values when applied the field [POSIBLE DESCRIPCION PRODUCTO COMERCIAL](Product) as first hierarchy....

Best Regards