Calculation of data in a table visual

[Anonymous]

Hi,

Recently started working with Power BI again, & just wanted to get the forum's help for the issue below-:

I have a huge raw data. Which has three columns for an example as shown below-:

Table 1-

Char A	Char B	Value
M1	A1	8759933399
M2	A2	9435545332
M1	A2	9454324332
M2	A3	7434324332
M3	A3	9434324332

Now i have to show it in matrix visual with the "Value" being counted distincly.

Table 2-

	A1	A2	A3
M1	1	1	0
M2	0	1	1
M3	0	0	1

I have a second table which has conversion-factors:

Table 3-

Char A	Conversion factor
M1	0.1
M2	0.4
M3	0.35

Now, I need to divide the value in Table 2 with conversion factors in table 3 to get new set of data. So, my final output should also look like Table-2 but just with converted values.

I am not able to figure out how to achieve this on POWER BI, as all the calculation seems to happen on raw-data level. But, in my case I need to do calculations on data from visual.

Kindly provide suggestions to do so.

Thanks in advance!

[Anonymous]

Hi,

Thank you so much, it worked! I had to also turn-off the "Row subtotals", as that was creating two rows(one with a total) for each conversion factor.

Just a follow-up question, how can I also see the Grand-total(sum along the column) as well. The matrix visual doesn't automatically show the Grand-total after following your instructions above.

Thanks a lot for your help!

[Anonymous · Accepted solution]

Hi @Anonymous , you can use the above measure but the problem is this will work only when you have Aconversion Factor in your metrix, so what you have to do is you have to put this Aconversion Factor in rows and then remane the column with 'Space' button just like below. 

 after that you have to turn off the Text wrap in Row Headers.

Then expand down to next level of Hierarchy.

then you will have this below thing.

After that just hide the conversion factor column by adjusting column header then you will get you final output.

you can also remove that expand/collapse symbol before M1,M2,M3 column by clicking and turning off +/- icons in Row Headers.

If this works for you then please mark my solution as answer.