topic Re: Help on making calculations of columns in a matrix table in DAX Commands and Tips

Help on making calculations of columns in a matrix table

Marango — Fri, 30 Aug 2024 03:58:28 GMT

Hello!

to make it ilustrative, I made this Matrix table so In the cloumns the first raw is divided in "MUESTRAS", "DUPS" AND "PRODUCCIÓN", each one has two quantities "modelos" and "unidades". I need to make some calculations.

1) need to see the percentage of how many "mdelos-producción" are from "modelos-muestras", in excel I will just do this : (modelos producción)cell / (modelos muestras)cell -- and change the number to %

2) I need to do also the following formula:

total "unidades"/ 300

it would be great if I could add it in the same matrix table, but if I need to do it separately I can make it, but really need this two KPIS.

Thank you!@links to meme

Re: Help on making calculations of columns in a matrix table

Rupak_bi — Fri, 30 Aug 2024 06:26:48 GMT

Please share sample data. and desired output for validation.

Re: Help on making calculations of columns in a matrix table

Anonymous — Mon, 02 Sep 2024 02:35:13 GMT

Hi Marango ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.

(2) We can create measures.

Percentage Measure = var _modelos_producci=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Cliente]=MAX('Table'[Cliente]) && 'Table'[Tipo de Venta]="Producción" && 'Table'[Type2]="Modelos")) var _modelos_muestra=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Cliente]=MAX('Table'[Cliente]) && 'Table'[Tipo de Venta]="Musetra" && 'Table'[Type2]="Modelos")) RETURN DIVIDE(_modelos_producci,_modelos_muestra)

total unidades = var _a=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Cliente]=MAX('Table'[Cliente]) && 'Table'[Type2]="Unidades")) RETURN DIVIDE(_a,300)

(3) Then the result is as follows.

Due to the nature of the matrix, Percentage Measure and total unidades will appear under each category, if you don't want so many duplicates, you can manually scale the columns to hide them. Or display them as visual objects in a table.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.

Re: Help on making calculations of columns in a matrix table

Marango — Fri, 06 Sep 2024 22:32:58 GMT

Hi,

sorry for my late reply. I am not able to upload my dar base so I am sending a wetransfer with the PBIX and an excel file with the same date. in the excel in the "results" sheet I highlighted in yellow what I want to get.

https://we.tl/t-t5mMhi0JmC

thank you