DAX - Multiply filtered row by multiple columns and show in visualization

[FrancoGR]

Hi, please your help. I've been trying to combine in a matrix report:

1. Table A. each row represent a task and, de type of distribution (this is variable) and the cost of the task

2. Table B. The kind of distribution that affects echa task

I need in a matrix report and abstract of each Dist and its cost distribuited en echa center.

Adding an example for better explanation.

Sorry about my english. Thanks

Franco Guevara

[parry2k]

@amitchandak yes that will work but then needs to create four measures, and if I have to solve for this I will stay away from that but not a bad solution. Cheers!!

[parry2k]

@amitchandak unpivot will not work because it will create many to many relationships and in that case, RELATED will not work.

[amitchandak]

@parry2k , I mentioned that solution under "Otherwise join"

Sorry, If that was not clear @parry2k , @FrancoGR .

There are two solutions. one assuming 1-M join, Assuming B on one side

[amitchandak · Accepted solution]

@FrancoGR , Unpivot the table B in power query and merge it with Table A, and then create a new column that is the multiplication of % from B and Costo from A

https://radacad.com/pivot-and-unpivot-with-power-bi
Append : https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Otherwise join the two in Model view assuming B on 1 side

Create new measures

Center W m = Sumx(TableA, TableA[Costo] * related(TableB[Center W]) )

same way others , examples

Center X m = Sumx(TableA, TableA[Costo] * related(TableB[Center X]) )

[FrancoGR]

@amitchandak 
thank You. Worked perfectly.

Franco GR