topic Re: Add a column to a table and summarize into it based on 2 filters in DAX Commands and Tips

Add a column to a table and summarize into it based on 2 filters

Foguete — Wed, 02 Nov 2022 14:59:45 GMT

Hello,

As I am new to Power BI, I am facing an issue.

I have the below table:

I would need for everyday, to know the total of "Pallets_Stock" of grouped "AUX".

For example.

Let's say AUX "187N", "170N", and "030N" are part of a family I want to create called "Family 1"; I would need the return below:

I need to say that an AUX can also we used in another family.

Anyone could help me but also explain to me the way ?

Kind regards

Re: Add a column to a table and summarize into it based on 2 filters

Greg_Deckler — Wed, 02 Nov 2022 15:22:10 GMT

Foguete Well, probably best to create an Enter Data query like below. You could create a relationship between the tables based on the AUX columns. It will be many-to-many but that shouldn't cause an issue in most circumstances.

Family	AUX
Family 1	187N
Family 1	170N
Family 1	030N
Family 2	777N
Family 2	666N
Family 2	555N
Family 3	187N
Family 3	555N
Family 3	457N

Re: Add a column to a table and summarize into it based on 2 filters

Foguete — Thu, 03 Nov 2022 08:35:19 GMT

Hi Greg_Deckler, thanks for the answer !

I have been creating the data entry and edit the relationship as the screenshot below:

Now I am facing a new issue, at the moment of creating a new table to get something similar to the screenshot below:

My DAX is telling me that it does not found the "Family" column (I changed the name family for "Tiendas"):

Any idea about what did I do wrong ?

Re: Add a column to a table and summarize into it based on 2 filters

Foguete — Thu, 03 Nov 2022 09:52:01 GMT

Hi again,

I think I have found the solution or at least it's working

Instead of SUMMARIZE, as I was using different columns I used SUMMARIZECOLUMNS which gave this result in DAX:

Tabla_Stock_Informe =

SUMMARIZECOLUMNS(

QueryKeynes[JDFEC],

'Filtros Tiendas'[Tienda],

"TOTAL PALLETS_STOCK", SUM(QueryKeynes[PALLETS_STOCK])

)

Thank you for the help !