Totalizar montos de factura si y solo si incluyen un articulo especifico

[DanyVega]

Buenas tardes Comunidad.

Recurro a ustedes con la siguiente inquietud,

Tengo un requerimiento en el cual me piden identificar los folios de facturas que incluyen un listado de productos especificos. una vez identificados esos folios, necesito con DAX obtener el valor total de los documentos. 

Adjunto imagen del ejemplo de lo que busco obtener. si alguien pudiera aportarme o bien sugerirme alguna otra opción que no sea con DAX les agradeceré bastante.

Gracias.

[nandic]

Hi @DanyVega ,

There are several functions which might be interest for you:
Filter - to filter records based on criteria
Sumx - to sum only amount of filtered data
ContainsString, IN, = (any other operator) to set criteria

Total Amount List =

var _FilterList = FILTER('Table',CONTAINSSTRING('Table'[Codigo Producto],"BLACK"))

RETURN

SUMX(_FilterList,'Table'[Monto])

If you display only Folio and new measure it will display only these relevant folios.

Regards,

Nemanja Andic

[DanyVega]

Hi Nandic, 

Let me see this example.

Is there a way that in the new measure it sumarizes the total of all the lines when it matches the item id?

Example: 

X1 = 3,069.25 + 5,559.11

X2 = 0 or null (Because this folio doesn't have an item BLACK)

Thank you again.

[nandic · Accepted solution]

Hi @DanyVega ,
Attached new version, i missed that part, my mistake.
As long as field Codigo Producto is not displayed, this measure will work.
Below is screenshot with example: total is same on all visuzalizations, but on last where we have field Codigo, detail rows are not correct.
If your dataset is huge, this might have low performance because of Concatenax function.

Total Amount List New =

VAR _CriteriaFolio =

     ADDCOLUMNS (

        SUMMARIZE (

          FILTER (

             SUMMARIZE (

                 'Table',

                 'Table'[Folio],

                 'Table'[Monto],

                "_criteriaText", CONCATENATEX ( 'Table', 'Table'[Codigo Producto] )

            ),

        CONTAINSSTRING ( [_criteriaText], "BLACK" )

      ),

   'Table'[Folio]

  ),

 "Sum", CALCULATE ( SUM ( 'Table'[Monto] ) )

)

RETURN

SUMX ( _CriteriaFolio, [Sum] )

Regards,
Nemanja Andic

[DanyVega]

Hi Nandic

I have a new problem 😑

I need display in another table the integration of all items there are in those folios,

But when i don't select any folio table show all of items (not only folios included "items" black).

When i selected some folio, table of bottom show me perfectly items with $ integration.

Would you help me again please?

[nandic]

Hi,

I will need these information:
1) what is different in table 3 comparing to first 2 tables? Are all fields (columns, measures) from one table or from multiple tables?
2) if fields are from multiple tables, please send screenshot of model (relationships between tables)
3) in third table, are details row correct, but row for total is incorrect, or both (detail and total row) are incorrect?

Regards,
Nemanja Andic

[DanyVega]

Hi Nandic

All data are in one table. All field are same. 

From the third table individually the value is correct, but I need only to show those that match the condition that I comment on below, which at the end adds up to $ 866,225

The logic I need for the third table is the following:

Show all items with their individual amount as long as the invoice id includes a black item. Similar to how it is shown when I select an invoice id from the first table (as I show you in the second image)

[DanyVega]

I solved it, although not in the way I expected.

First, I created a table in the model to keep the folios that include the "black" and added a column to them with the value 1.

Then, to the sales table I added a column with the lookupvalue function to get that value 1 in all the matching rows.

Lastly, I created a measure with Calculate and condition that last column to the value 1.

In this last image we can see that without applying any filter, the amounts in all the tables match me.

[DanyVega]

Hi Nandic.

Omg, that's wonderfull. You're a master 😎

Thank you so much!