Forum Discussion
Variance measure problem
Hi Qiuyun Yu,
Thanks for getting back to me on this. I have tried applying the logic in your reply however I am still struggling to get this to work.
Each row in my data relates to an individual customer finace application and contains information about the customer and their application. One of the fields is the 'year of application'. This value is only a year value between 2011-2015. Each customer and therefore ID can appear multiple times each year dependent on how many finace applications they have submitted to the company. I therefore want to count how many total customer applications were made each year and then show the actual +/- change in the count of applications . I have mocked up some data below as an example of what im looking for. Ideally the chart should change when I use a slicer to select type of application or another value in the actual data.
| RAW Data | ||
| Customer ID | Year of Application | Type of Application |
| 123 | 2011 | CC |
| 123 | 2011 | Loan |
| 196 | 2014 | CC |
| 168 | 2015 | CC |
| 136 | 2013 | CC |
| 842 | 2012 | Loan |
| 648 | 2015 | CC |
| 123 | 2011 | Loan |
| 847 | 2015 | Loan |
| 952 | 2014 | CC |
What the waterfall should show:
| Year | Count | Difference |
| 2011 | 3 | |
| 2012 | 1 | -2 |
| 2013 | 1 | 0 |
| 2014 | 2 | 1 |
| 2015 | 3 | 1 |
| Total | 10 |
Apologies if there is a really simple solution to this however I am just beginning to get to grips with Power Bi. I hope this makes sense and thank you for any further help you can offer.
Matt
Hi aa8057,
In your scenario, you can create a new table via expression:
Table = SUMMARIZE('RAW',RAW[Year of Application],"Count",COUNTA(RAW[Type of Application]))
Then create two calculated columns:
CountPrevious = calculate(SUM('Table'[Count]),filter('Table','Table'[Year of Application]=EARLIER('Table'[Year of Application])-1))
Difference = if('Table'[CountPrevious]=0, BLANK(), 'Table'[Count]-'Table'[CountPrevious])
For details, you can download the attached .PBIX file.
Best Regards,
Qiuyun Yu
The solution worked for me! Thanks.
How would i incorporate a breakdown by month too? If I wanted to see the total count for this month versus this month last year?