Forum Discussion
Difference between 2 Cell values in a Matrix
Hi All,
I am trying to achieve this simple feature in Power BI but due to my limited knowledge in Power BI, I am not able to achieve this.
I have the following data.
| Jun-17 | Jul-17 | Aug-17 | Sep-17 | Oct-17 | Nov-17 | Dec-17 | Jan-18 | Feb-18 | Mar-18 | Apr-18 | May-18 | Jun-18 | |
| Estimated Submissions | 100 | 104 | 108 | 112 | 116 | 120 | 124 | 128 | 132 | 136 | 140 | 144 | 148 |
| Estimated Applications | 50 | 54 | 57 | 60 | 63 | 66 | 69 | 72 | 75 | 78 | 81 | 84 | 87 |
| Estimated Fundings | 10 | 13 | 16 | 19 | 22 | 25 | 28 | 31 | 34 | 37 | 40 | 43 | 46 |
| Actual Submissions | 50 | 51 | 52 | 53 | 54 | 55 | 56 | 57 | 58 | 59 | 60 | 61 | 62 |
| Actual Applications | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 |
| Actual Fundings | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 |
Now I just want to add a new row below which just performs Actual Submissions - Estimated Submissions, Actual Applications - Estimated Applications and so on as mentioned below.
| Deviation Submissions | -50 | -53 | -56 | -59 | -62 | -65 | -68 | -71 | -74 | -77 | -80 | -83 | -86 |
| Deviation Applications | -30 | -33 | -35 | -37 | -39 | -41 | -43 | -45 | -47 | -49 | -51 | -53 | -55 |
| Deviation Fundings | 0 | -2 | -4 | -6 | -8 | -10 | -12 | -14 | -16 | -18 | -20 | -22 | -24 |
Can anyone give me some pointers as to how to achieve this?
Thanks,
Vijeth
Hi vijeth_sankethi,
I entered some data to do a test. Based on my test, you can refer to below steps in query editor:
This picture show the data I entered.1.Pivot your [column3],choose the Values Column [Column2].
2.Add the three Custom Columns to show your data.
Deviation Submission=[Actual Submissions]-[Estimated Submissions]
Deviation Application=[Actual Applications]-[Estimated Applications]
Deviation Fundings=[Actual Fundings]-[Estimated Fundings]
3. Unpivot all of your columns except the [Column1].4.Create a Matrix visual to show your data.
For more information, see attached pbix file.
Best Regards,
Qiuyun Yu
2 Replies
Hi vijeth_sankethi,
I entered some data to do a test. Based on my test, you can refer to below steps in query editor:
This picture show the data I entered.1.Pivot your [column3],choose the Values Column [Column2].
2.Add the three Custom Columns to show your data.
Deviation Submission=[Actual Submissions]-[Estimated Submissions]
Deviation Application=[Actual Applications]-[Estimated Applications]
Deviation Fundings=[Actual Fundings]-[Estimated Fundings]
3. Unpivot all of your columns except the [Column1].4.Create a Matrix visual to show your data.
For more information, see attached pbix file.
Best Regards,
Qiuyun YuHi v-qiuyu-msft,
I was trying something on the similar lines. Thanks for putting a lot of effort to get this resolved. I will accept this solution.
Thanks,
Vijeth
