Forum Discussion
Need conditional Count/Running Total from a Data Set
Hi Gurus
I'm new to Power BI and we are in transition planning from Excel Pivots to Power BI dashboards. I've been given a test case to check the compatibility of Power BI with our scenario. I've following data (which is pulled from SSAS cubes):
AgeBand ProgramYear ProgramCode Trimester StudentNumber 15-20 2015 Psychology T1 111111 15-20 2015 Psychology T1 111112 15-20 2015 Psychology T1 111113 21-25 2015 Psychology T1 111114 21-25 2015 Psychology T1 111115 21-25 2015 Psychology T1 111116 26-30 2015 Psychology T1 111117 26-30 2015 Psychology T1 111118 26-30 2015 Psychology T1 111119 15-20 2015 Psychology T2 111110 15-20 2015 Psychology T2 111112 15-20 2015 Psychology T2 111113 21-25 2015 Psychology T2 111121 21-25 2015 Psychology T2 111122 21-25 2015 Psychology T2 111115 21-25 2015 Psychology T2 111116 26-30 2015 Psychology T2 111117 26-30 2015 Psychology T2 111118 26-30 2015 Psychology T2 111119 15-20 2015 Psychology T3 111123 15-20 2015 IT T1 111111 15-20 2015 IT T2 111111 15-20 2015 IT T3 111111 15-20 2015 IT T3 111112
From this data I need two kinds of outputs.
First one is:
| Psychology | ||||||||||||
| Year | 2015 | |||||||||||
| Trimester | T1 | T2 | T3 | Psychology Total | ||||||||
| AgeBand | New Enrolments | Re-Enrolments | Total Enrolments | New Enrolments | Re-Enrolments | Total Enrolments | New Enrolments | Re-Enrolments | Total Enrolments | New Enrolments | Re-Enrolments | Total Enrolments |
| 15-20 | 3 | 0 | 3 | 1 | 2 | 3 | 1 | 2 | 3 | 5 | 4 | 9 |
| 21-25 | 3 | 0 | 3 | 2 | 2 | 3 | 0 | 0 | 0 | 5 | 2 | 7 |
| 26-30 | 3 | 0 | 3 | 0 | 3 | 3 | 0 | 0 | 0 | 3 | 3 | 6 |
I am planning to build this output throuh Matrix layout by having Trimesters in columns whereas user will select Year & Program from Slicers. But I can't calculate New & Re-Enrolments figures in Power BI.
The second output I need is:
| Year | Program | Trimester | Age | New Enrolments | New Running Total | Re-Enrolments | Re-Enrolments Running Total |
| 2015 | Psychology | T1 | 15-20 | 3 | 3 | 0 | 0 |
| 2015 | Psychology | T1 | 21-25 | 3 | 6 | 0 | 0 |
| 2015 | Psychology | T1 | 26-30 | 3 | 9 | 0 | 0 |
| 2015 | Psychology | T2 | 15-20 | 1 | 10 | 2 | 2 |
| 2015 | Psychology | T2 | 21-25 | 2 | 12 | 2 | 4 |
| 2015 | Psychology | T2 | 26-30 | 0 | 12 | 3 | 7 |
| 2015 | Psychology | T3 | 15-20 | 1 | 13 | 2 | 9 |
| 2015 | IT | T1 | 15-20 | 1 | 1 | 0 | 0 |
| 2015 | IT | T2 | 15-20 | 0 | 1 | 1 | 1 |
| 2015 | IT | T3 | 15-20 | 1 | 2 | 1 | 2 |
Same problem I've in this output is to differentiate between New & Re-Enrolment fiures and then to have their runnings totals.
Can you please help me to build up this report?
3 Replies
Hi kamran,
>>But I can't calculate New & Re-Enrolments figures in Power BI.
After research, I am still confusing how to calculate the New and Re-Enrolments. For example, in output table2, how to get 1 in 15-20 age, T2Trimester, 'Psychology'ProgramCode, 2015?
In addition, in Excel Pivot, you get the result by creating calculated or measure? You can also create calculated column and measure in Power BI.
Could you please share more details for further analysis?
Best Regards,
AngeliaHi Angelia
Thanks a lot for looking into my problem.
>>After research, I am still confusing how to calculate the New and Re-Enrolments. For example, in output table2, how to get 1 in 15-20 age, T2Trimester, 'Psychology'ProgramCode, 2015?
New Enrolment means, any student appearing for the first time in a program (Psychology or IT) in our data set is New. If it appears 2nd or 3rd time in subsequent years or trimesters but in the same program, it should be considered as Re-Enroling student.
>> In addition, in Excel Pivot, you get the result by creating calculated or measure? You can also create calculated column and measure in Power BI.
I've generated this output with SQL and I tried to generate BI report with Advanced option of Direct Query, but there are limitations on using SQL query as a data source e.g use of Common Table Expression in the Query, application of Query parameters etc.
Moreover, I found DAX formulae for Running Totals but these are straight running total for the whole data set. What if I wish to break/group running totals for each Qualification Programs?
Hi kamran,
Many fucntions are not used in Direct Query model. I recommand you load you data using import model.
Then you can create measures using the formulas, and get expected result.New Enrolments = CALCULATE(COUNTA(Test[StudentNumber]),FILTER(Test,Test[time]=1)) Total = CALCULATE(Test[New Enrolments],FILTER(ALL(Test),Test[Index]<=MAX(Test[Index])))
Then create a matrix visual shown the first screenshot, and second visual shown in the following second screenshot.
If you have any question, please let me know.
Best Regards,
Angelia
